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 |
|
davidshq
Posting Yak Master
119 Posts |
 Posted - 2005-10-23 : 01:01:38
|
| Lets say you take user input and make it into varA and that the user enter's "My name is Jim. My mom's name is Mary." You then insert this statement into a table, and of course, it throws an error. This is because there was a single quote in the data the user entered.How can one allow one's users to use single and double quotes in their input and at the same time avoid errors? I know that if Jim put, "My name is Jim. My mom''s name is Mary." This would work. But who wants their users to have to do that. And what about regular quotation marks, "Mark said, "I am big.""? Thanks.David.- http://www.civilwarsearch.com/- http://www.thehungersite.com/- http://www.grid.org/ |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-23 : 01:40:31
|
Well, if its in a SQL variable it ain't a problem, so I assume this is in some application language, like Basic, and you are using Dynamic SQL calls rather than Stored Procedure calls (which also wouldn't have the problem - unless they in turn used dynamic SQL).For Basic you would need to use REPLACE to "double up" the single quotes (the double quotes won't be a problem) - something like:MyStringVar = replace(MyStringVar, "'", "''") Note that if you do NOT do this you are open to SQL Injection attacks. Here's a slightly different string from the one in your example:My name is Jim. My mom'; DROP DATABASE ...  Kristen |
 |
|
|
davidshq
Posting Yak Master
119 Posts |
Posted - 2005-10-23 : 01:52:48
|
| Kristen, Thanks for the advice. Yeah, I am using ASP.NET 2.0. I found the StringBuilder method and did exactly what you said. The one area I am still wondering about is double quotes ("). Is there a way to use the StringBuilder method on them?David.- http://www.civilwarsearch.com/- http://www.thehungersite.com/- http://www.grid.org/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-23 : 03:22:05
|
| I can;t think of an instance where double quotes will be a problem - if you've got a piece of code that brakes SQL post it here and I'll have a look!Kristen |
|
|
|
|
|
|
|
|
SQL Quotes, Variables, & Errors.