Single Quote issue

[fniles]

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'
Else
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
cmd.ExecuteNonQuery()
Loop

 

[Herfried K. Wagner [MVP]]

I am looping thru DataReader and constructing a sql query to insert to
another database.

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.

 

[Guest]

When the data type of the field is string I insert the field value
using a single quote.

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

When the value of the field has a single quote in it like "O'Toole",
how can I construct the string ?

Use SQL Parameters please!


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

http://msdn2.microsoft.com/en-us/library/ms161953.aspx




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

 

[Harry Strybos]

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'
Else
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
cmd.ExecuteNonQuery()
Loop

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!

 

[Bill Nguyen]

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

Bill


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