Escaping a single quote 2

[skiflyer]

I'm using SQL server and I want to run a simple query... well it's not so simple... but a simple version can suffice to answer my question...

Namely, I want..

INSERT INTO blah (somecolumn) VALUES ('john's boat')

I somehow need to escape that internal ', but can't figure it out... I'm fairly certain it's positive because if I just open the table and modify values I get no objections... / doesn't seem to work, nor does // or \\ or any such combo of those... nor does "john's boat"... and those are all the tricks I have up my sleeve... anyone have the answer?

-Rob

 

[DanJR]

Hi there,

You need to add an extra single quote for each single quote that appears within the string.

i.e.,
INSERT INTO blah (somecolumn) VALUES ('john''s boat')

However, in terms of excaping the single quote in SQL Server, then is this what you are after?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_9jxu.asp

Other relevant links:

http://www.sqlteam.com/item.asp?ItemID=1649

http://www.sqlteam.com/item.asp?ItemID=202

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q178070&

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_10162576.html

Cheers,
Dan

 

[DanJR]

Oh, here's another link about control query behaviour in SQL Server

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=7635&pg=2