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.GetFieldT ype(x) Is GetType(String) Or
drSQL.GetFieldT ype(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.ExecuteNonQ uery()
Loop

[Spam Catcher]

Re: Single Quote issue

"fniles" <fniles@pfmail. comwrote in
news:ur$8sILnHH A.1244@TK2MSFTN GP04.phx.gbl:

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'

[Herfried K. Wagner [MVP]]

Re: Single Quote issue

"fniles" <fniles@pfmail. comschrieb:

>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.

--
M S Herfried K. Wagner
M V P <URL:http://dotnet.mvps.org/>
V B <URL:http://dotnet.mvps.org/dotnet/faqs/>

[Harry Strybos]

Re: Single Quote issue

"fniles" <fniles@pfmail. comwrote in message
news:ur$8sILnHH A.1244@TK2MSFTN GP04.phx.gbl...

>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.GetFieldT ype(x) Is GetType(String) Or
drSQL.GetFieldT ype(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.ExecuteNonQ uery()
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]

Re: Single Quote issue

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(strValu e, "'", "''")

End Function

"Harry Strybos" <harry_NOSPAM@f fapaysmart.com. auwrote in message
news:OpjQdiLnHH A.4412@TK2MSFTN GP02.phx.gbl...

"fniles" <fniles@pfmail. comwrote in message
news:ur$8sILnHH A.1244@TK2MSFTN GP04.phx.gbl...

>>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.GetFieldT ype(x) Is GetType(String) Or
>drSQL.GetField Type(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.ExecuteNonQ uery()
> 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!
>