Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Single-Quotes are causing problems!

SQL coding

Single-Quotes are causing problems!

by  chiph  Posted    (Edited  )
Use ADO parameters. They take care of this problem, plus the related problem with using the double-quote character.

They're a little more effort to code, but if you're doing multiple inserts into the same table, or multiple selects with different value crtieria, they end up running faster than concatenating a SQL string together.
Code: 
Dim adoConn as ADODB.Connection
Dim adoComm as ADODB.Command
Dim adoParamUserName as ADODB.Parameter
Dim adoParamPWD as ADODB.Parameter
Dim sSQL as String
Dim i as Long

sSQL = ""
sSQL = sSQL & " INSERT INTO tbl_User"
sSQL = sSQL & "   (UserName, Password)"
sSQL = sSQL & " VALUES (?, ?)"

Set adoConn = New ADODB.Connection
adoConn.ConnectionString = "My Connection String"
adoConn.Open
Set adoComm = New ADODB.Command
adoComm.ActiveConnection = adoConn
adoComm.CommandText = sSQL

Set adoParamUserName = adoComm.CreateParameter("UserName", adString, adParamInput, 20)
Set adoParamPWD = adoComm.CreateParameter("Password", adString, adParamInput, 20)

adoComm.Parameters.Add adoParamUserName
adoComm.Parameters.Add adoParamPWD

For i = 1 to NumUsers
   adoParamUser.Value = User(i)
   adoParamPWD.Value = PWD(i)
   adoComm.Execute , , adExecuteNoRecords
Next i

Set adoParamUser = Nothing
Set adoParamPWD = Nothing
Set adoComm = Nothing
If Not adoConn Is Nothing Then
    If adoConn.State = adStateOpen Then
        adoConn.Close
    End If
    Set adoConn = Nothing
End If
Hope this helps.

===============================
Update: 2003-09-09

Since I wrote this FAQ many years ago, I've learned more about why using ADO parameters is a good idea.

Primarily, the best reason to use parameters is to protect yourself against a cracking attack known as SQL Injection. This happens because the programmer used string concatenation to build their SQL. If your SQL looks like this:
Code: 
sSQL = ""
sSQL = sSQL & " INSERT INTO tbl_User"
sSQL = sSQL & "   (UserName, Password)"
sSQL = sSQL & " VALUES ('" & sUsername & "', '" & sPassword & "')"
you are vulnerable to this attack. What happens is the attacker, instead of using a valid username or password, uses a value that looks like this: [color red]';DROP TABLE tbl_User --[/color] so that when it gets seen by the database, it looks like this: [color blue]INSERT INTO tbl_User (UserName, Password) VALUES (';DROP TABLE tbl_User; --', 'Mypasswordvalue')[/color]

If you analyze what happens here, the SQL execution engine says "fine..fine..Oh, a semicolon." The semicolon terminates the first part of the statement, and it gets executed. It will error out, but because there is still more to the statement to do, it keeps going.

It then says "Oh, I need to drop table user". Which it does (uh-oh!). The rest of the statement is ignored, because the double-dash is SQL's comment indicator.

So guess what has happened -- instead of adding a user to your table, your attacker has instead dropped all users from your database. Do you have a backup? I hope so, because they're all gone now.

It gets worse.

What if the attacker were to send you a string that looks like this:[color red]';EXEC xp_cmdshell Format C: --[/color]

When the SQL engine runs this, it will execute the xp_cmdshell stored procedure. And the parameter that gets passed to it will format your C: drive. I hope you really have a backup, as your operating system just got wiped. Putting your data files on drive D: is of no help, as the attacker might send multiple requests, starting at drive Z: and working his way down. I hope you didn't have any drives mapped to other machines!

Using ADO parameters prevents all this, as they will filter out these sorts of attacks. Yes, it's more work to code. But having to recover from one of these attacks is even less fun.

BTW: Don't forget that the attack can come from more than just your GUI - it can come from a Web form, an imported XML file, or a web service. Basic rule is: DO NOT TRUST DATA FROM THE USER.

=============================
Update 2003-09-11

Here are some links from Zemp that explain the seriousness of needing to use ADO parameters:

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

http://www.devarticles.com/content.php?articleId=138
(taken from Thread333-287702)

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

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

=============================
Update 2006-10-19

Added length parameters to the calls to CreateParameter. SQL Server is ok without them, but it appears that MS-Access wants them.

Chip H.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top