1. Introduction
2. Be Paranoid
3. Escape All Single Quotes
4. Limit User Input Width
5. Only GRANT what the User Needs
6. Catch Errors
7. Sample Code #1

Introduction

Users are evil. As a Visual Basic developer writing a complex database application that uses MySQL, you may already feel this way as you receive feature request after feature request, all of which absolutely have to be included, without any extension in the project deadline (of course!). But when I say that users are evil, I am speaking from a security standpoint. You have a database full of valuable information, information you (or your non-evil users) do not want to wind up in the wrong hands. And even if your database is full of public information, you still don’t need someone crashing the server or otherwise gumming up the works. And yet that someone is out there, waiting for his/her chance to exploit your code and ruin your day.

Now there are a lot of ways for a malicious individual to ruin your day, they can hack the server MySQL is running on and shut it down, or maybe flood your server with requests until your application is unusable. They could walk into your office while nobody is looking and rip that shiny new quad-Opteron server right out of the rack and take it home. There are as many ways to ruin your day as there are creative people determined to ruin it, but the topic of this article will be SQL injection attacks.

What is a SQL injection attack?

A SQL injection attack involves a malicious user adding more to your query than you originally intended. Using such an attack, a malicious user can extract sensitive information, modify/drop tables, delete users, and cause all sorts of mayhem.

Lets give an example:

A typical SQL injection attack might be to bypass your applications login screen. Lets assume you have a form that queries for username and password in txtquery and txtpassword, then takes those and retrieves a count of matching rows from your user table. If the count is greater than zero, we have a valid user. We could also do a SELECT * with the provided credentials, and if we do not have an EOF (end of file) on our resulting recordset, we also know the user is valid.

Our table would look like the following:

CREATE TABLE login (
       username varchar(20) NOT NULL,
       password varchar(20) NOT NULL);

And here is the click event for the form’s submit button:

Private Sub cmdLogin_Click()
       Dim SQL As String
       Dim conn As ADODB.Connection
       Set conn = New ADODB.Connection

       Dim rs As ADODB.Recordset
       Set rs = New ADODB.Recordset

       conn.CursorLocation = adUseClient
       conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
       & "SERVER=127.0.0.1;" _
       & "DATABASE=test;" _
       & "UID=root;" _
       & "PWD=12345;" _
       & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

       conn.Open SQL = "SELECT * FROM login WHERE username = ‘" & txtUsername.Text & "’ AND password = ‘" & txtPassword.Text & "’"

       rs.Open SQL, conn, adOpenStatic, adLockReadOnly

       If rs.EOF Then
           MsgBox "Invalid Login"
       Else
           MsgBox "Logged in as " & rs!username
       End If

       rs.Close
       Set rs = Nothing

       conn.Close
       Set conn = Nothing
       End Sub

Now, when I type ‘mike’ as a username and ‘12345’ as a password (which match what I assigned in the table as username and password), I get the message “Logged in as Mike” when I click the login button (note that you do not types the quotes). And as a good, conscientious user, this is the only way I would every use this form.

Of course, not everyone is a good little user such as myself. What do you think would happen if I types in the following as my username, with anything entered into the password box: ‘ OR 1=1 #

Lo and behold, I once again receive the message “Logged in as Mike”. How you may ask? Lets look at the query that gets sent to the MySQL server when I use ‘ OR 1=1 # as my username:

SELECT * FROM login WHERE username = ‘’ OR 1=1 # AND password = ‘’

The OR clause causes any row to pass the WHERE clause, because 1=1 is always true (at least up here in Canada). The hash symbol (#) causes the MySQL parser to ignore the rest of the statement. Therefore for all intents and purposes this query becomes:

SELECT * FROM login

Which returns all rows from the table. Whether the original query uses count or just a check for EOF, the rows returned will be greater than zero, and your user will be logged in without a valid username or password. If you wanted to be logged in as a specific user, you could use ‘ OR username = ‘bob’ # in your username, specifying that you wish to login as bob.

UNION is a Two Edged Sword

UNION is a powerful SQL keyword. It allows you to retrieve the results of multiple queries with a single statement. This can be very useful when you need to retrieve data using WHERE restrictions that cannot be done with JOINs, or when you need to join a table in two different ways. One example would be if you are comparing two tables and you want a list of what is present in one and not in the other, and vice-versa; this involves two left joins between the two tables, and would normally require two queries. Thanks to the UNION statement, you can connect the two SELECT queries together and retrieve all the data with one query. The problem is that an evil user can use the UNION statement to add more information the your form’s listview than you intended.

Lets say you have a search form that displays the result of your user’s search to a listview control or bound datagrid (actually any form that allows a user to scroll through the resultset of a query will be vulnerable). The following sample will make use of the ListViewLoad function. Lets assume a form with a listview named lvwResult, a textbox named txtName, and a command button called cmdSubmit:

Private Sub cmdSubmit_Click()
       Dim SQL As String
       Dim conn As ADODB.Connection
       Set conn = New ADODB.Connection

       Dim rs As ADODB.Recordset
       Set rs = New ADODB.Recordset

       conn.CursorLocation = adUseClient
       conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
       & "SERVER=10.1.1.2;" _
       & "DATABASE=test;" _
       & "UID=root;" _
       & "PWD=12345;" _
       & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

       conn.Open

       SQL = "SELECT username FROM login WHERE username LIKE ‘%" & txtUserName.Text & "%’"
       rs.Open SQL, conn, adOpenStatic, adLockReadOnly

       ListViewLoad lvwResult, rs
       rs.Close
       Set rs = Nothing

       conn.Close
       Set conn = Nothing
       End Sub

By entering ‘mike’ (without quotes) into the txtUserName text box, I will find a list of all users that match ‘mike’ displayed in the listbox. As long as your users can be trusted, this code is fine as-is, but lets remember that Users Are Evil.

If I were to enter ‘ UNION SELECT password FROM login # in the txtUserName textbox, I would be given a list of all users and their passwords, because my query is now:

SELECT username FROM login WHERE username LIKE ‘%’ UNION SELECT password FROM login #%’

The LIKE ‘%’ clause will return every username, and the UNION SELECT password FROM login # query gives us all the passwords. This combination would leave our evil user with some very valuable information.

As you look at the above examples of SQL Injection, you may think that a potential attacker would have to know a certain amount about the structure of your tables to successfully execute an attack using a UNION. You would be correct, but do not relax your defenses simply because your table structure is unknown. This article is not meant to be a howto on SQL injection against VB and MySQL, but lets consider it sufficient to say that an attacker can use malformed SQL to extract table information from error messages returned by MySQL.

Multi-Query Execution

One attack that MySQL developers have been protected from is the injection of separate SQL queries into their own queries. Developers using other DBMSes have been subjected to attacks along the lines of ‘; DROP TABLE login # I would find myself missing a login table, and a fair amount of application functionality.

Because MySQL does not currently support the execution of multiple queries separated by semicolons, developers using MySQL have been protected from this attack. However, this functionality is available starting in MySQL 4.1, though it will be disabled by default (in order to avoid having unknown developers and webmasters upgrade their database and find themselves vulnerable to a previously unknown attack. Connector/ODBC (formerly MyODBC) version 3.52 will have an option available to activate this functionality, but use it only after you understand this article, and at your own risk.

Defending Against SQL injection Attacks

There are a number of steps you can take to help prevent SQL injection attacks, I will go over some of the common methods below.

Be Paranoid

Hopefully my examples above are adequate to get you feeling that SQL injection is something that you should take seriously. If I have done my job well, then I have made you paranoid, and that is a good thing! You simply cannot trust any user input, whether it has just been entered into your form, or whether you have retrieved it from the database. Any time you are dynamically generating SQL you must exercise all due diligence to make sure that nothing insidious is being injected into it.

Escape All Single Quotes

The first (and probably most logical) step in the fight against SQL injection is to keep users from using single-quote characters maliciously. Your first thought may be to strip out the single-quote characters, but this also entails discriminating against any users who have an apostrophe in their name (”Sorry Mr. O’Neill, but you cannot use our software”). A better solution is to escape the single-quote character. By escaping a character, we send the character to the MySQL server preceded by a backslash (\) character to indicate that the character is not to be interpreted by the server but just included as part of a string.

The function located at the bottom of this article will call the MySQL API function mysql_escape_string, which can be used to escape all single quote and backslash characters from untrusted strings.

Passing any dynamic portions of your SQL queries through such a function will go a long way towards the prevention of SQL injection with your VB/MySQL application.

Limit User Input Width

The user column of this table is a varchar(20), yet a VB textbox can hold billions of characters by default. This situation leaves the evil user a lot of room to write his SQL injection code. You can set the MaxLength property of a textbox control (and most other text input controls) to a specific character length, this preventing the user from entering more than the necessary 20 characters. In situations where MaxLength is not available, the left function works nicely.

Only GRANT what the User Needs

If a user only needs to SELECT data from a table, do not grant the user all privileges. While it is easier to type GRANT ALL than to type GRANT SELECT(columnA,columnC,columnE), it is terribly insecure.

Catch Errors

Errors returned by the MySQL server are very useful to you as a developer. The error message will tell you approximately where the error is in your query by return some of the query that follows the error-causing syntax. If that error message reaches you end user, the small portion of your original query that is returned with the error message will give the Evil User clues as to the underlying table structure and the query you use to query the database. Some hackers will deliberately use SQL injection to place error causing syntax into your query to divine your table’s structure.

Use error catching with the On Error syntax to return more generic error messages to the user. These more generic error messages will thwart Evil Users while allowing legitimate users to report errors to you.

Conclusion

In this article I have show you some examples of SQL injection and ways to counteract them. It is my hope that you have recognized SQL injection as a legitimate concern to you as a developer and that you take steps to include security in your application from design to deployment.

Sample Code #1

Download Attached Samplecode

Expanding on the string escaping function previously available at http://www.vbmysql.com/samplecode/stripquote.html, this function uses the built-in function in the MySQL client API

‘—————————————————————————————
‘
‘    Copyright 2003 Mike Hillyer (www.vbmysql.com)
‘
‘    This program is free software; you can redistribute it and/or modify
‘    it under the terms of the GNU General Public License as published by
‘    the Free Software Foundation; either version 2 of the License, or
‘    (at your option) any later version.
‘
‘    This program is distributed in the hope that it will be useful,
‘    but WITHOUT ANY WARRANTY; without even the implied warranty of
‘    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
‘    GNU General Public License for more details.
‘
‘    You should have received a copy of the GNU General Public License
‘    along with this program; if not, write to the Free Software
‘    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
‘
‘    Please forward all questions/suggestions to mike@vbmysql.com
‘
‘
‘ Module    : modEscapeString
‘ DateTime  : 1/2/2004 08:39
‘ Author    : MIKE HILLYER
‘ Purpose   : THIS MODULE HOLDS THE CODE NEEDED TO IMPLEMENT MYSQL_ESCAPE_STRING
‘             IN A VISUAL BASIC 6 PROJECT. IT SHOULD BE NOTED THAT MySQL HAS DEPRECIATED
‘             MYSQL_ESCAPE_STRING IN FAVOR OF MYSQL_REAL_ESCAPE_STRING BUT SINCE
‘             THE LATTER REQUIRES AN ESTABLISHED CONNECTION IT ADDS A LOT OF
‘             COMPLEXITY FOR A DEVELOPER WHO IS CURRENTLY USING MYODBC. WE COULD USE
‘             MYSQL_REAL_ESCAPE_STRING WHEN USING AN ALL-API APPROACH TO USING MySQL
‘             AND OMITTING MYODBC ENTIRELY.
‘                  SEE http://www.MySQL.com/doc/en/mysql_escape_string.html
‘
‘             USER BEWARE: THIS FUNCTION IF FOR USE WITH LATIN(DEFAULT)
‘                          CHARACTER SETS ONLY, IT WILL NOT NECESSCARILY
‘                          WORK WITH NON-LATIN CHARACTER SETS!
‘
‘—————————————————————————————
Option Explicit
 
‘API DECLARATION FOR mysql_escape_string FUNCTION CALL
Public Declare Function api_mysql_escape_string Lib "libmySQL.dll" _
        Alias "mysql_escape_string" _
        (ByVal strTo As String, _
         ByVal strFrom As String, _
         ByVal lngLength As Long _
        ) As Long
 
Public Function mysql_escape_string(dirtystring As String) As String
Attribute mysql_escape_string.VB_Description = "Calls libmysql.dll mysql_escape_string function to clean a string for insertion into MySQL database. THIS DOES NOT LOOK AT CURRENT DATABASE CHARACTER SET!"
    Dim strFrom As String           ‘SOURCE STRING PASSED TO FUNCTION
    Dim lngFromLength As String     ‘LENGTH OF SOURCE STRING
    Dim strTo As String             ‘DESTINATION STRING COMING FROM FUNCTION
    Dim lngToLength As Long         ‘LENGTH OF DESTINATION STRING

    strFrom = dirtystring           ‘STORE FUNCTION INPUT
    lngFromLength = Len(strFrom)    ‘GET LENGTH OF INPUT

    strTo = Space(lngFromLength * 2 + 1) ‘ALLOCATE A BUFFER FOR OUTPUT OF FUNCTION
                                         ‘2 BYTES PER CHARACTER PLUS A BYTE FOR NULL
                                         ‘TERMINATOR USED BY FUNCTION

    lngToLength = api_mysql_escape_string(strTo, strFrom, lngFromLength) ‘CALL API

    mysql_escape_string = Left(strTo, lngToLength) ‘TRIM NULL TERMINATOR
End Function