Single Quote issue

  • Thread starter Thread starter fniles
  • Start date Start date
F

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
 
fniles said:
I am looping thru DataReader and constructing a sql query to insert to
another database.
Click to expand...

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.
 
When the data type of the field is string I insert the field value
using a single quote.
Click to expand...

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 ?
Click to expand...

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'
 
fniles said:
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
Click to expand...
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!
 
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
 
Back
Top