August 21, 2003 at 8:48 am
Hi there,
I am having a problem for a sp which allows user to insert a sql statment string into a table and use the sql statement later. How do I convert the string into a SQL Server acceptance string format but runnable later on? Is my problem make sense? Anyone can help?
Thanks,
AC
August 21, 2003 at 9:09 am
No problem doing this. The biggest factor you need to keep in mind is storing the quotes properly as though you were building a dynamic SQL statement.
Remember to pay attention to date handling. You may want to store tokens in your string if the query needs to compare GETDATE().
Guarddata-
August 21, 2003 at 9:18 am
Sorry, I don't get it. Can you give me more explicit explanation for how should I solve this problem?
Thanks,
AC
August 21, 2003 at 9:24 am
I ran into this problem a while back. I was building an application in PHP that accessed a SQL Server database and one thing I included was a timestamped log with any queries that the users ran.
I ended up building two functions in PHP, one to encode the sql string into something that SQL Server didn't try to execute and another that decoded it into an executable string.
Of course, that was before I discovered PHP's magic quotes function...
Strictly within SQL Server you might try something similar with functions or a stored procedure.
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
August 21, 2003 at 9:39 am
I am thinking, if the application could replace all the single quotes in the sql string into a rarely used character before passing in the string. In this case, I will have no problem to insert the string into the table. I then replaced the character back into a single quote when running it. This should work, isn't it?
AC
August 21, 2003 at 9:55 am
That's exactly what I did in PHP and it worked fine. Although, it seems to me there was some other character that needed replacing as well.
"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)
August 21, 2003 at 9:56 am
Normally, this should not be a problem if you can execute stored procedures from the front-end you are using.
If you pass in the SQL string as a parameter, SQL Server will never treat this as executable code.
CREATE PROCEDURE InsertSQLString
@sqlstring varchar(8000)
AS
INSERT INTO sqltable (sqlstring)
VALUES(@sqlstring)
August 21, 2003 at 10:02 am
That is what my initial design for my sp. I basically taking whatever the pass in string and simply insert the value in the table. But if the application pass in a string like -> SELECT * FROM abc WHERE name LIKE 'A%'. Of course, there will be a single quote in the beginning and at the end of the string. It will failed because of the single quote.
AC
August 21, 2003 at 11:24 am
I have had occasion where the double quotes also cause problems with the ADO object. I am hoping this was a "previous version" bug.
Basically - any string where you could
EXEC sp_executesql N@myStr
you could also
INSERT INTO SqlCmdSave ( MyCommand ) VALUES ( @myStr )
The only problem I see is for date sensitive information. SELECT * FROM TimeSlip WHERE WorkDate < GETDATE()
You need to decide is this a snapshot (i.e. before 11:20 AM on August 21) or current (same query no matter when it is run)
Hope this helps
Guarddata-
August 22, 2003 at 1:19 am
Still don't see the problem with the quotes (well, depending on your environment).
In Visual Basic you would do something like :
Dim oCmd as new ADODB.Command
Dim oParam as ADODB.Parameter
oCmd.ActiveConnection = <your connection object>
oCmd.CommandText = 'InsertSQLString'
Set oParam = oCmd.CreateParameter('sqlstring', adChar, adParamInput, 5, txtSQL.Text)
oCmd.Execute
In this sample, the txtSQL can be for example a textbox in which the user enters his/her SQL String that needs to be stored.
No problems using quotes here. And you don't have to be afraid of a SQL Injection attack, since the parameter will be treated as such!
August 22, 2003 at 8:16 am
Yes, I do agree with the gentleman, who said it was not a problem if u could pass the query thru front end, the sql compiler would treat as a string and u could use it in constructing a query and executing it. For example u could have a string variable @str1 for SELECT statement, @str2 for FROM, @str3 for WHERE and use in a parent variable @STR and execute the @STR. This will enable u'r user to construct queries at a detailed level. Hope, this helps you. All the best.
August 22, 2003 at 8:22 am
So, you can try a simple test like this and see what will happened! No to say INSERT into a table.
DECLARE @SQL VARCHAR(5000)
SET @SQL = 'SELECT * FROM PSSL_PROJECT WHERE PROJECT_ID = '0''
EXEC (@1)
AC
August 22, 2003 at 8:44 am
Obviously, that example won't work. What I was describing is a way to do this using any front end.
To escape a single quote, you should put two single quotes.
Your example would be
DECLARE @SQL VARCHAR(5000)
SET @SQL = 'SELECT * FROM PSSL_PROJECT WHERE PROJECT_ID = ''0'''
EXEC (@SQL)
August 22, 2003 at 9:03 am
OK, may be I should mentioned about the requirements. I have no control on what a user creates a "creative" sql statement and pass in. Therefore, I cannot binding the user to type a query that he wanted. Therefore, you solution won't work for my case.
AC
August 22, 2003 at 10:30 am
Can you please explain more clearly what you want to achieve?
Do you want to have a syntax checking done before inserting the SQL String?
Give some examples. My solution I posted earlier using VB will work for your example in the thread.
quote:
SELECT * FROM abc WHERE name LIKE 'A%'
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply