Subscribe to Blog.Parse(typeof(PsychoCoder), Richard McCutchen);        RSS Feed
***** 1 Votes

For Safety Sake - Parameterized Queries

Icon 2 Comments
In this series 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 parameterized 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.

Quote

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 ' OR '1' = '1 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

Quote

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

Quote

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 Parameterized Queries. Using parameterized 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 parameterized 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 SqlParameterCollection Class to create a parameterized 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

Quote

exec sp_executesql N'SELECT COUNT(userId) FROM users WHERE userName = @username AND password = @password,'N@username varchar(15),'N'@password varchar(25)',@username='yourname',@password='yourpassword'


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

2 Comments On This Entry

Page 1 of 1

CharlieMay Icon

19 June 2010 - 11:39 AM
Very nice example and explanation.

Just wanted to add that the
string sql = "SELECT COUNT(userId) FROM users WHERE userName = @username AND password = @password";

Works in Access too.

Also, as a solution to another problem people have with the the user inputting apostrophes and quotes, the parameters handle this for you along with parsing and determining date/times. In access you enclose a date with # instead of ' but with the parametrized query, it is handled.
0

raziel_ Icon

28 June 2010 - 02:38 PM
Brilliant it is sad we cant rep. blog entries.
0
Page 1 of 1

Trackbacks for this entry [ Trackback URL ]

There are no Trackbacks for this entry

April 2014

S M T W T F S
  12345
6789101112
131415 16 171819
20212223242526
27282930   

Recent Entries

Search My Blog

0 user(s) viewing

0 Guests
0 member(s)
0 anonymous member(s)