escaping nightmare

[QatQat]

HI there,

i am busy writing an intranet system, using MySQL as backend.

Now the system is to be implemented in Italy and single quotes are very common in the language.

I have already made so that, when single quotes appear in a string, they get replaced with \' correctly before inserting them in the table's cell.

Now I need to query already escaped values from the table.

An example perhaps will clarify my problem.

Let's assume that there is a field called Surname that has to store the value "D'AURIA"

By replacing single quotes with \' I managed to avoid errors in inserting the record.
if I "select * from tablename" I can visualise the Surname field populated with "D\'AURIA"


Now I need to build a select statement to allow users to search by surname.
Therefore I need to escape both backslash and single quote.

I tought of something like

select * from tablename where Surname like 'D\\\'AURIA%'

I get no error but no results either.


ANy suggestion?

QaTQat


If I could have sex each time I reboot my server, I would definitely prefer Windoz over Linux!

 

[r937]

you should be using standard sql escaping, not the proprietary mysql backslashes (which only lead to confusion)

your inserts should look like this --

INSERT 
  INTO daTable 
     ( ... , surname , ... )
VALUES 
     ( ... , 'D''AURIA' , ... )

and your queries should look like this --

SELECT ...
  FROM daTable
 WHERE surname = 'D''AURIA'

simply code two single quotes in a row for each single quote that is part of the string value




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[QatQat]

Thanks r397,


that did it.

I used the double single quotes with SQL server but I did not think it worked with MySQL as well.

Thanks again

QatQat

If I could have sex each time I reboot my server, I would definitely prefer Windoz over Linux!