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 |
|
oahu9872
Posting Yak Master
112 Posts |
 Posted - 2005-11-16 : 12:53:09
|
| i am having a problem with my database forms. if anywhere in the form the ' character is typed in, the sql insert statement fails. i've tried using varchar, nvarchar, and text as the field types with no luck. any ideas how to fix this? thanks |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2005-11-16 : 12:57:17
|
| Yep, replace 1 ' with 2 '________________________________________________SQL = Serious Quaffing of Liquor |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-16 : 15:37:54
|
| You need a client side function that you use to process the parameters:strValue = REPLACE(strValue, "'", "''")and use this on ALL parameters that you pass to SQL Server [using dynamic SQL], otherwise you are leaving yourself wide open to SQL Injection.Supposing I type into one of your formfields:FOO'; DELETE * FROM SomeTable; --how will your system handle that?If your dynamic SQL is:"INSERT INTO MyTable (COl1, Col2) VALUES (" + strParameter1 + "," + strParameter2 + ")"this will translate into INSERT INTO MyTable (COl1, Col2) VALUES (FOO'; DELETE * FROM SomeTable; --, BAR)the first part of which is going to generate a syntax error, and the second part is going to start deleting data.The example I have given is extremely naive, and won't work in practice - I am not about to describe the "101 steps to hacking a server", but the information is readily available, and you need to make sure your Forms Field Data, and dynamic SQL, is adequately protected, and the least you should do is to REPLACE Single Quotes to "double them up"Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-17 : 00:03:33
|
| Single quote will always give problem when your data have it and you do DMLs, searches,etcIn your presentation layer replace all single quotes to two single quotes by using Replace function as suggestedMadhivananFailing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-17 : 00:52:03
|
"Still you need to handle single quote"Only if you still called them with SQL Command strings  In my tests that adds 5% execution time, compared to the AppendParameter type client-side syntax for calling SProcs, so moving to Sprocs also means moving to AppendParameter type calling [in my book!]Kristen |
|
|
|
|
|
|
|
|
SQL won't accept ' character