Escaping ' charactors in SQL

  • Thread starter Thread starter Andrew Banks
  • Start date Start date
A

Andrew Banks

I'm having problems with some charactors in SQL statements.

For example, the following would give me an error.

UPDATE Table SET Column1 = 'This statement's not too long' WHERE ID = '2'

I can see that the error's due to the ' in statement's. I'm assuiming I need
to escape this somehow. How would this be done?

Thanks in advance
 
Andrew:

In most instances, use Parameters and this won't be a problem:

myCommand.CommandText = "Update Table SET Column1 = @FirstStatement Where ID
= '2'"
myCommand.Parameters.Clear
myCommand.Parameters.Add("@FirstStatement", SqlDbType.Whatever).Value = '2'

Tibor is correct that you can replace the single quotes with Doubles and in
QA or EM, this is the way to go. However, if you are using the command
object, it's made to handle this and every other such problem, giving you
added performance, greatly increased security and you won't have to worry
about you or someone else forgetting to add the double quotes.

This article should help you
http://www.knowdotnet.com/articles/storedprocsvb.html (Although this deals
with calling a parameterized stored proc, the exact thing works for SQL
Statements.

Cheers,

Bill
 
private string prepSQL(string SQLInput)
{
return SQLInput.Replace("'","''");
}

To use, simply do the following:

SQLString = "UPDATE Table SET Column1 = '" & PrepSQL(problemStatement) & "'
WHERE ID = '2'"

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************
 
You may use paramters too, either SQLParameter or OleDbParameter. It will do
the escaping for you. It need a little bit more effort, but you will get the
benifit of data type validation, reusing the parameterized SQL statement,
etc.

Patrick Zhang
MCSE, MCSD
 
Back
Top