Connecting to a Database Securely

To protect against database vulnerabilities such as SQL injection attacks, user input should be verified, and statements that connect to databases should be built securely. This topic provides example code that uses placeholders (also called parameterized commands) to connect to a database.

Common Mistake

A common mistake when connecting to a database is to use form input to construct a database query string in the following manner:

strSQL = "select * from MyTable where Name = '" & Request.Form("Name") & "'"

This allows a malicious user to send the following string as the Name variable:

Mary Graham' or 1=1 --

The resulting query string would look like the following:

select * from MyTable where Name = 'Mary Graham' or 1=1 --'

This query string would return the expected record for Mary Graham, but it would also return any record where 1=1, which is every record in the MyTable table. SQL injection might also be used to create, add, or delete records.

Many developers believe that quoting string inputs or calling stored procedures mitigates this problem, but those methods do not provide full protection.

Using Parameterized Commands

Using placeholders (also called parameterized commands) provides the best protection against SQL injection attacks. When you define a query, you determine which parts of the statement are parameters. For example, the following is a parameterized version of the previous query, with an additional parameter for a password:

select * from MyTable where Name=? and Pwd=?

Example Code

The following example shows you how to use Visual Basic Scripting Edition (VBScript) to parameterize a connection string. This example connects to a SQL server named MyServer to verify that a user name and password exists, while protecting against SQL injection attacks.

This example was taken from the chapter titled "Database Security Issues" of the book titled Writing Secure Code (ISBN 0-7356-1722-8).

xFunction IsValidUserAndPwd(strName, strPwd) 

    ' Use a trusted connection to SQL server. Never use the sa account. 
    strConn = "Provider=sqloledb;" +_ 
        "Server=MyServer;" +_ 
        "database=MyTable;" +_ 
        "trusted_connection=yes" 
    Set cn = CreateObject("ADODB.Connection") 
    cn.Open strConn 

    Set cmd = CreateObject("ADODB.Command") 
    cmd.ActiveConnection = cn 
    cmd.CommandText = _ 
        "select * from MyTable where Name=? and Pwd=?" 
    cmd.CommandType = 1 'adCmdText 
    cmd.Prepared = True 

    ' Explanation of numeric parameters: 
    ' data type is 200, varchar string 
    ' direction is 1, input parameter only 
    ' size of data is 32 chars max. 
    Set parm1 = cmd.CreateParameter("Name", 200, 1, 32, "") 
    cmd.Parameters.Append parm1 
    parm1.Value = strName 

    Set parm2 = cmd.CreateParameter("Pwd", 200, 1, 32, "") 
    cmd.Parameters.Append parm2 
    parm1.Value = strPwd 

    Set rs = cmd.Execute 
    IsValidUserAndPwd = False 
    If 1 = rs(0).value Then IsValidUserAndPwd = True 

    rs.Close 
    cn.Close 

End Function