HELP!! Handling quotation marks in SQL insert

[SmileyFace]

Hey Guys. Really need your help with this one.

I am inserting data into an SQL server table from my asp page but need a function to handle the single quotation marks. I basically need to read the string being inserted into the table field and replace any single quotation marks (') with double quotes (&quot before inserting else I will get an error. PLEASE HELP me with this!! Would really appreciate it if someone could help me with the script.

Thanks a lot in advance!

 

[zemp]

See thread709-1526.

Thanks and Good Luck!

zemp

 

[zemp]

Here is an example of an insert statement using ado parameters that I use for addresses.

Private Sub AddNew_Address()
'// Add a new Property address to the database.
   Dim l_strSQL As String
   Dim rs As ADODB.Recordset
   Dim cmd As ADODB.Command
   
   On Error GoTo ERR_AddNewAddress
   
   l_strSQL = "SET NOCOUNT ON;INSERT INTO [Address] ([Street1],[Street2],[City_ID],[Province_ID],"
   l_strSQL = l_strSQL & "[PostalCode],[Country_ID],[Entered],[EnteredBy]) VALUES "
   l_strSQL = l_strSQL & "(?,?,?,?,?,0,?,?);SELECT @@IDENTITY AS ID;SET NOCOUNT OFF;"
      
   With m_udtProperty
      Set cmd = New ADODB.Command
      cmd.ActiveConnection = pmCONN
      cmd.CommandText = l_strSQL
      cmd.Parameters.Append cmd.CreateParameter("Street1", adVarChar, adParamInput, 50, Left$(.Street1, 50))
      cmd.Parameters.Append cmd.CreateParameter("Street2", adVarChar, adParamInput, 50, Left$(.Street2, 50))
      cmd.Parameters.Append cmd.CreateParameter("City_ID", adInteger, adParamInput, , .City_ID)
      cmd.Parameters.Append cmd.CreateParameter("Province_ID", adInteger, adParamInput, , .Province_ID)
      cmd.Parameters.Append cmd.CreateParameter("PostalCode", adVarChar, adParamInput, 10, Left$(.PostalCode, 10))
      cmd.Parameters.Append cmd.CreateParameter("Entered", adDBTimeStamp, adParamInput, , Date)
      cmd.Parameters.Append cmd.CreateParameter("EnteredBy", adVarChar, adParamInput, 20, Left$(g_strUsername, 20))
      Set rs = cmd.Execute
      .Address_ID = rs.Fields("ID").Value
   End With
   Set rs = Nothing
   Set cmd = Nothing
   Exit Sub
   
ERR_AddNewAddress:
   If Err.Number = 94 Then    '// Invalid use of null.
      Resume Next
   Else
      ErrorMessenger Err.Number, Err.Description, "frmProperty.AddNew_Address", "Property Error"
      Set rs = Nothing
      Set cmd = Nothing
   End If
End Sub

Thanks and Good Luck!

zemp

 

[bjd4jc]

zemp-

what did your thread link have to do with this discussion?? type-o maybe??

 

[zemp]

Sorry, I was trying to link to faq709-1526.

Thanks and Good Luck!

zemp

 

[SmileyFace]

Hey Thanks Zemp! I am sure this will be useful.

 

[chiph]

Geeze, I had forgotten I wrote that FAQ!

SmileyFace - I've just updated that FAQ with more recent info regarding use of ado parameters.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[mmilan]

Can't he just replace all single apostrophes with double apostrophies?

 

[CCLINT]

>Can't he just replace all single apostrophes with double apostrophies?
And if there are "double apostrophies" in the field value as well?
Or with "double apostrophies" did you mean two single ones?

The user should be able to enter the printable characters desired.
With the current ADO model, either replacing the single quotes with TWO single quotes, or using a Command and a Parameter object, are the only options I would consider at all.

 

[chiph]

mmilan -
Given the chances of a SQL Injection attack, I would say that using ADO Parameters is your only choice.

Doubling up on the single & double-quote characters doesn't protect you from that.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[zemp]

I agree with chiph and CCLINT, using the ADO parameters is the safest and best option. See faq7091526 for more info. Thread222-640856 also has some information and more links.

Here are some links to more information as well,

http://www.palecrow.com/content/GCIH/Matt_Borland_GCIH.html

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetch12.asp

Thanks and Good Luck!

zemp

 

[CCLINT]

chiph is correct in reducing the choice to just using a Command object for SQL Server, and, if so, then why not for all providers?

With something like JET/ACCESS it wouldn't/shouldn't matter.
JET uses the Sandbox mode starting with JET 3 SP 3 to prevent unsafe commands (Shell!) from being executed.
But we like to keep things scalable, don't we?

 

[chiph]

CCLINT -

You're right - with the recent versions of JET you don't have this problem.

But you probably already know my feelings towards MS-Access!

ragger fragger rakker toy database mutter mutter mutter ....
;-)

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first