using apostrophe in sql statement

djanim8 · October 5th, 2006, 09:44 AM

I'm having an error with my ASP page when a person puts an apostrphe into a field to be entered into a sql database.. here is a test on the sql statement:

SELECT * FROM tblRequests WHERE firstname="Test" AND lastname="Test'O" AND checktype="Bankruptcy" AND dateEntered="10/5/2006"

here is the code that puts it together:

Code:

  newSQL="SELECT * FROM tblRequests WHERE firstname=" & Chr(34) & MBRequest("txtFirst") & Chr(34) & ""
  newSQL=newSQL & " AND lastname=" & Chr(34) & MBRequest("txtLast") & Chr(34) & ""
  newSQL=newSQL & " AND checktype=" & Chr(34) & Session("CheckType") & Chr(34) & ""
  newSQL=newSQL & " AND dateEntered=" & Chr(34) & FormatDateTime(now(),2) & Chr(34) & ""

The error I get is this:

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
ODBC driver does not support the requested properties.
/secur-it/background/submitorder.asp, line 125

can anyone help me out with this?

GillBates · October 10th, 2006, 09:09 AM

you need to escape all single quotes with another single quote as bar minimum

So

Denis O'Reilly

becomes

Denis O''Reilly

(with two single quotes instead of one single quote).

Use a function that does a replace... e.g.

Function SQLSafe(strText)
SQLSafe = Replace(strText, "'", "''")
End Function

Then you can put SQLsafe(xxx) around whatever values you link into your SQL query

Failing to sanitize scripts like this is more dangerous than you think. Google for SQL injection and prepare to cr4p your pants.