Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ArthurC
Starting Member
7 Posts |
 Posted - 2003-08-21 : 10:40:29
|
| Hi there,I am having a problem for inserting SQL statement string into a table. Since the system allows end-user to create his own sql statement and stores it into a table for later use. There is no control for the user's creation. Here I come to a problem if the user created something like 'SELECT * FROM abc WHERE name LIKE 'A%''. Since the application will pass in the string with a single quote and it causes syntax error. I am intended to create a sp/function to clean it up before inserting into the table but how and what should I clean up. I made a simple test for converting the single quote into double for a simple case, it works. However, there will be a prblem when I tried run the sql statement because double quote is not the same as single quote. Anyone could help?Thanks,AC |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-08-21 : 10:45:58
|
| Convert single quotes to '' (two single quotes). This will allow you to insert the row. Then when you seletct it back again it will come back to you as '-------Moo. :) |
 |
|
|
ArthurC
Starting Member
7 Posts |
Posted - 2003-08-21 : 10:51:45
|
| Do you mean convert the string like "SELECT * FROM abc WHERE name LIKE "A%"" this before insert it into table? And then converses it back into single before running the sql string? It is a good idea but is there any other issue? I don't know.AC |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-08-21 : 11:03:07
|
| You would store"SELECT * FROM abc WHERE name LIKE ''A%''"yes.It is stored in SQL as single quotes only. You do not have to convert it back on the way out. There are no other issues that I can think of. Apart from the inherent danger posed by allowing the users to run whatever they like, I suppose.-------Moo. :) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-08-21 : 11:03:40
|
| use a stored procedure with paramters to add rows to the table, do NOT build "INSERT" statements dynamically. then, you won't have this problem. just assign the parameter the value you need (your SQL statement) without any formatting or changing at all, and then execute the stored procedure.- Jeff |
|
|
|
ArthurC
Starting Member
7 Posts |
Posted - 2003-08-21 : 11:13:41
|
| I am using a sp in which one of the IN parameters pass in the sql statement string, I simply insert the string value into a table but it causes error. I am trying to use what Mr Mist suggested. Will see!AC |
|
|
|
|
|
|
Insert SQL statement into a table