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.
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
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:
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: