Single Quote issue

Single Quote issue

Post by fnile » Thu, 24 May 2007 05:45:42

I am looping thru DataReader and constructing a sql query to insert to
another database.
When the data type of the field is string I insert the field value using a
single quote.
When the value of the field has a single quote in it like "O'Toole", how can
I construct the string ?

Do While drSQL.Read
sSQL = "insert into " & sTableName & " values ("
sSQL2 = ""
For x = 0 To iFieldCnt - 1
If IsDBNull(drSQL.Item(x)) Then
sSQL2 = sSQL2 & ",null"
ElseIf drSQL.GetFieldType(x) Is GetType(String) Or
drSQL.GetFieldType(x) Is GetType(Date) Then
sSQL2 = sSQL2 & ",'" & "''" & drSQL.Item(x) &
'" -> WHEN the value is O'Toole this becomes 'O'Toole'
sSQL2 = sSQL2 & "," & drSQL.Item(x)
End If
Next x
sSQL2 = Mid(sSQL2, 2, Len(sSQL2))
sSQL = sSQL & sSQL2 & ")"
cmd.CommandText = sSQL -> WHEN the value of 1 of the field
is O'Toole this becomes "Insert into myTable values ('O'Toole') and got an
error when executing the ExecuteNonQuery

Single Quote issue

Post by Herfried K » Thu, 24 May 2007 06:10:22

"fniles" < XXXX@XXXXX.COM > schrieb:

Use parameterized command objects instead of building the whole connection
string including the values on your own in order to prevent such problems
and potential security problems caused by SQL injection.

M S Herfried K. Wagner
M V P <URL: ;
V B <URL: ;


Single Quote issue

Post by Spam Catch » Thu, 24 May 2007 06:10:26

"fniles" < XXXX@XXXXX.COM > wrote in

Don't construct a SQL string with parameters on the fly - this is ripe for
SQL injection attacks.

Use SQL Parameters please!

What is a SQL Injection Attack and how to use SQL Parameters:

P.S. But if you insist on being careless ;-), to insert a single quote you
would double it... i.e. 'O''Toole'

Single Quote issue

Post by Harry Stry » Thu, 24 May 2007 06:31:57

You need to put two single quotation marks eg O''Toole (not a double
quotation). Having said that, PLEASE take note of what the other guys are
saying...use paramerized stored procs!

Single Quote issue

Post by Bill Nguye » Thu, 24 May 2007 07:19:23

Put your SQL clause in this function:
for example strQuoteReplace(sSQL)


Public Shared Function StrQuoteReplace(ByVal strValue As String)

' Replace any single quote in strValue with two single quotes.

' The second argument to Replace consists of

' one single quote enclosed in a pair of double quotes.

' The third argument to Replace consists of

' two single quotes enclosed in a pair of double quotes.

StrQuoteReplace = Replace(strValue, "'", "''")

End Function