INSERT INTO Crashes with Apostrophe 5

[ClulessChris]

I use the following statement it insert into an Access database:

g_sSQL = "INSERT INTO tblRequests (sSurname, sInitials,sNino,bFromCCC, sRequested" & _
                ",sAddress, sDetails, sFrom, dSent,dImported)" & _
                "VALUES('" & Field_Arr(0) & "','" & Field_Arr(1) & "','" & Field_Arr(2) & "'" & _
                ",'" & Field_Arr(3) & "','" & Field_Arr(4) & "','" & Field_Arr(5) & "','" & Field_Arr(6) & "'" & _
                ",'" & Field_Arr(7) & "','" & Field_Arr(8) & "','" & Now & "')"
                
        dbFormRequests.Execute g_sSQL

However should one of the fields I try to insert contain an apostrophe (e.g. O'Neil) the statement crashes. Please help.

Everybody body is somebodys Nutter.

 

[Transcend]

You need to replace ' with ''

g_sSQL = "INSERT INTO tblRequests (sSurname, sInitials,sNino,bFromCCC, sRequested" & _
",sAddress, sDetails, sFrom, dSent,dImported)" & _
"VALUES('" & Field_Arr(0) & "','" & Field_Arr(1) & "','" & Field_Arr(2) & "'" & _
",'" & Field_Arr(3) & "','" & Field_Arr(4) & "','" & Field_Arr(5) & "','" & Field_Arr(6) & "'" & _
",'" & Field_Arr(7) & "','" & Field_Arr(8) & "','" & Now & "')"

g_sSQL = replace(g_sSQL, "'", "''")

dbFormRequests.Execute g_sSQL

Transcend

 

[ClulessChris]

Transcend
Thanks for your input however I still get the same error.

Everybody body is somebodys Nutter.

 

[Transcend]

So what error do you get?

Transcend

 

[Transcend]

Does this work without apostrophes??

I just realised that you have quotes in your sql so you might be better off doing

g_sSQL = "INSERT INTO tblRequests (sSurname, sInitials,sNino,bFromCCC, sRequested" & _
",sAddress, sDetails, sFrom, dSent,dImported)" & _
"VALUES('" & replace(Field_Arr(0), "'", "''")
& "','" & Field_Arr(1) & "','" & Field_Arr(2) & "'" & _
",'" & Field_Arr(3) & "','" & Field_Arr(4) & "','" & Field_Arr(5) & "','" & Field_Arr(6) & "'" & _
",'" & Field_Arr(7) & "','" & Field_Arr(8) & "','" & Now & "')"


dbFormRequests.Execute g_sSQL

This only replaces quotes in the surname, you should add the replace function to any other variables which may contain quotes.

Transcend

 

[ClulessChris]

Yes, Yes, Yes, That worked just GREAT. Many thanks.

Everybody body is somebodys Nutter.

 

[Transcend]

Glad to help

Transcend

 

[zemp]

A cleaner, safer option - faq709-1526.

zemp

 

[ClulessChris]

Zemp

"A cleaner, safer option - faq709-1526"

I Don't see much difference here apart from the connection, ADO rarther than DAO, is this what you're driving at or have i missed something?

Everybody body is somebodys Nutter.

 

[zemp]

SQL Injection attacks. With a regular insert statement they are possible and can drop your entire database.

Sorry I assumed that you were using ADO and was just offerring an alternative solution.

zemp

 

[bjd4jc]

Zemp was talking about the use of the command object and parameters in which case you will avoid the downfall of having to account for characters such as the apostrophe and others as well as SQL Injection attacks.

 

[ClulessChris]

bjd4jc
Many thanks for the clarifacation.

Everybody body is somebodys Nutter.

 

[FailSafe]

Here is a VB Function which you can parse all your SQL Text through:

Public Function SQLEncode(sqlValue As String) As String
On Error Resume Next ' NOT really needed
SQLEncode = CStr(Replace(sqlValue, "'", "''"))
End Function


Brian Gillham
FailSafe Systems

 

[chiph]

Failsafe -
Your code (using the replace function to double-up the single quotes) leaves the SQL statement open to attack via SQL Injection. Please read the FAQ that zemp included in his post and you'll see why doing this is such a Bad Thing to do.

Second-worst case scenario for a SQL Injection attack is your drive gets formatted. The worst case is the attacker puts a backdoor on your system that you never detect.

Chip H.


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