replacing single quotes with 2 single quotes

[jimmywages]

Hi Y'all,

I'm trying to write a string to a database using SQL and since SQL doesn't like single quotes, I had to replace it with 2 single quotes...

Replace(MyString, "'", "''&quot

I thought this was an "escape" sequence... meaning 2 single quotes is interpreted as 1 single quote... But this is not the case.. When I look into the database, I see that it's added two single quotes inside there as well!

So (in the database) instead of: It's a nice day. I get: It''s a nice day.

How do I avoid actually adding 2 single quotes into the database yet not causing SQL to give me errors?

Any help would be appreciated!

~jimmy.

 

[JohnYingling]

SQL does not mind single quotes. However, the SQL statement parser dOes mind so you only double up single quotes if you are placing the data field into an SQL statement between two single quotes.

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[jimmywages]

Thanks! That solved my problem!

 

[JohnYingling]

BTW, the doubling up of single-quotes has a side benefit of preventing the "SQL Injection Attack". Enter "SQL Injection Attack" into

http://www.google.com

if you do not know what it is. On the display side, all data from a database should go thru HTMLENCODE before being passed to the browser to avoid puposeful &quot;Cross-Site Scripting Attacks&quot; or the occurence of a < or a > here and there in the data.

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript