The role of Parametrized queries

[PsychoCoder]

In this entry we’re going to be looking at ways you can protect yourself & application data from malicious attacks from outside sources. This first entry we’re be looking at parametrized queries to help protect against SQL Injection attacks. So many times I see people, especially new programmers, who are using code that interacts with a database that is formatted like so:

public bool LoginToSystem(string un, string pwd)
{
    int count = 0;
    using (var conn = new SqlConnection("YourConnectionStringHere"))
    {
        string sql = "SELECT COUNT(userId) FROM users WHERE userName = '" + un + "' AND password = '" + pwd + "'";
        using (var cmd = new SqlCommand(sql, conn))
        {
            conn.Open();
            count = (int)cmd.ExecuteScalar();
        }
    }
 
    return count > 0 ? true : false;
}

Now some will look at that example and at first think “Whats wrong with that?”. Well it’s called SQL Injection, and using code like shown above is prime and ready for this kind of attack.

• SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution.

Given the above example we could provide the values [il]‘ OR ’1' = ’1[/il] for both username and password values and we would be able to log in no problems, as doing this would cause this to be executed in your database:

• SELECT COUNT(userId) FROM users WHERE userName = ” OR ’1' = ’1' AND password = ” OR ’1' = ’1'

Since 1 always equals 1 someone would then have access to your system, and trust me this isn’t something you want to have happen. That’s a mild result of what could happen if the above sql statement stayed the way it is, so this would then produce the following script to be executed in your database. With the way the statement is formatted someone could add ;DROP TABLE users-- and this, as you can image, would have devastating consequences. If that were to execute then this would be what your database sees:

• SELECT COUNT(userId) FROM users WHERE userName = ” AND password = ”; DROP TABLE users–

The semi-colon tells the database that one execution is ending and another is beginning, the – at the end of the statement tells SQL that the rest of the statement is a comment so ignore it. What would this do, well it would delete your users table.

So what can you do about this, well one of the biggest steps you can take to protect yourself is by using Parametrized Queries. Using parametrized statements embed your values into the statement, making it that much harder for someone to inject commands into your code.

So let’s take a look at how the above scenario can be remedied by using parametrized queries to protect yourself. Keep in mind this example will be for MSSQL, but I will also show how to do this for Microsoft Access as well. To do this we will use the AddWithValue Method of the SqlParameterCol lection Class to create a parametrized query.

The new code (for MSSQL) would look like this:

public bool LoginToSystemParameterized(string un, string pwd)
{
    int count = 0;
    using (var conn = new SqlConnection("YourConnectionStringHere"))
    {
        string sql = "SELECT COUNT(userId) FROM users WHERE userName = @username AND password = @password";
        using (var cmd = new SqlCommand(sql, conn))
        {
            conn.Open();
            cmd.CommandType = System.Data.CommandType.Text;
            //now add our parameters
            cmd.Parameters.AddWithValue("@username", un);
            cmd.Parameters.AddWithValue("@password", pwd);
            count = (int)cmd.ExecuteScalar();
        }
    }
 
    return count > 0 ? true : false;
}

For Microsoft Access we would have to make a small modification to our statement to look like this

public bool LoginToSystemParameterized(string un, string pwd)
{
    int count = 0;
    using (var conn = new SqlConnection("YourConnectionStringHere"))
    {
        string sql = "SELECT COUNT(userId) FROM users WHERE userName = ? AND password = ?";
        using (var cmd = new SqlCommand(sql, conn))
        {
            conn.Open();
            cmd.CommandType = System.Data.CommandType.Text;
            //now add our parameters
            cmd.Parameters.AddWithValue("@username", un);
            cmd.Parameters.AddWithValue("@password", pwd);
            count = (int)cmd.ExecuteScalar();
        }
    }
 }
    return count > 0 ? true : false;
}

In MSSQL @parametername is a placeholder for the value we’re embedding into our statement, with Access you use a question mark ? for a place holder. When this new query is sent to SQL Server it’s executed by the system stored procedure

• sp_executesql. So to SQL Server this query looks like this
  exec sp_executesql N’SELECT COUNT(userId) FROM users WHERE userName = @username AND password = @password,’N@us ername varchar(15),’N' @password varchar(25)’,@u sername=’yourna me’,@password=’ yourpassword’

So as you can see using parametrized queries can go a long way towards protecting yourself, your application & data, and your employer/users from malicious attacks from outside sources