ERROR adding a phrase that has a apostrophy (') 3

[A1Pat]

I have a database that receive data from a form using Uploader.Form because I need to add an image as well. While adding new data, I got a ERROR message:

Desc : [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'idGuest <> 1 AND lcase(adImage) = '' AND adShortDesc = '0.75 x 150' Continuous Labels''.

SQL : SELECT * FROM ClassifiedAds WHERE idGuest <> 1 AND lcase(adImage) = '' AND adShortDesc = '0.75 x 150' Continuous Labels'

I believe that problem may has caused by the phrase in the adShortDesc that I included a apostrophy(') as feet which confused the SQL. I could change that ' to ft but that is not applicable because someone else may not like that. Is there a way to control input data when added so that SQL won't be confused and crashed like this? Thanks!

 

[Sheco]

You could use the Replace() method to change all ' to the similar character ` or you could "escape" the quote by doubling it x = replace(x, "'", "''")

Another option would be to use client-side javascript to prevent the character from ever being submitted.

Also, please do a google search for "sql injection attack" and read a few of the hits.

 

[A1Pat]

Thank you Sheco for your help...

I really want to keep all inputs authentical so replacing from (') to (") is not workable in this case (not to mention changing from ft. to inch.)

Another way of yours is replacing (') with (`) may be useable and I may need to change it back to (') when recalled. Unless there is another better or shorter way to solve the problem, this way will be applicable in the mean time.

thanks again!

 

[gmmastros]

Replacing a single apostrophe with 2 apostrophes will NOT actually put 2 apostrophes in to the database. A single apostrophe will appear instead.

I urge you to try it. I'm sure you will be pleasantly surprised.

Of course, if you use the ADO command object instead of building sql strings, you will be safeguarded against SQL Injection attacks AND your apostrophe problem will completely go away.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Sheco]

Escaping the single quote with SQL works just like escaping the slash when outputting characters in one of the curlybrace languages. (C/C++/JavaScript/etc)

It is just a note to the command parser that you actually want to use the special character as a character, without the special meaning.

To illustrate what I mean, run these 2 commands against your database:
[TT]SELECT 'That dog is named Steve'

SELECT 'Bob''s dog is named Steve'[/TT]

 

[A1Pat]

Is this what you meant:

adsdesc = trim(replace(Uploader.Form("adsdesc"),"'","''"))

with this line, the (') will be replaced with ('').

 

[DotNetGnat]

you have to use the replace() function at the query level and not before that...

-DNG

 

[A1Pat]

YUP!!! you all are right!

It works now!!

Thank you all!

 

[onpnt]

Seems the FAQ's aren't being utilized all that much anymore.

faq333-3048

Reference 12
Q: My SQL statement breaks when someone puts a single quote in my form
A: You should always replace single quotes before putting values from a form into an SQL statement, otherwise your user John O'Malley is going to have problems or you could fall prey to an SQL injection attack.
<%
Dim myString
myString = Replace(Request.Form("txtSample"),"'","''")
%>
The double single quotes makes it so the server doesn't think your string is finished, but will still input a single single quote into the entry your adding/comparing to.

General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[onpnt]

note: my post is not worth a star. The FAQ's deserve the stars and I just wanted to get the section noticed


General FAQ faq333-2924
5 steps to asking a question faq333-3811