INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

HANDLE


PASSWORD
Remember Me
Forgot Password?

Navigation

Join Us!
Keyword Search
Browse Forums
Jobs
Start A Group
Tell A Friend
Donate
Whitepapers

Come Join Us!

  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • Turn Off Ad Banners
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
E-mail*
Handle

Password
Verify P'word
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Member Feedback

"...I have tons of books, have book marked tons of tutorials, which have helped, but this forum has answered those "impossible to find" solutions. I am thrilled with this site..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!
Home > Forums > Programmers > Languages > Visual Basic(Microsoft) Databases > FAQs

Visual Basic(Microsoft) Databases FAQ

SQL coding

Single-Quotes are causing problems!
faq709-1526
Posted: 1 Feb 02 (Edited 19 Oct 06)

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: ';DROP TABLE tbl_User -- so that when it gets seen by the database, it looks like this:  INSERT INTO tbl_User (UserName, Password) VALUES (';DROP TABLE tbl_User; --', 'Mypasswordvalue')

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:';EXEC xp_cmdshell Format C: --

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.

Back to Visual Basic(Microsoft) Databases FAQ Index
Back to Visual Basic(Microsoft) Databases Forum
My FAQ Archive
Email This FAQ To A Friend

My Archive