Title: Protect a program from SQL injection attacks that steal passwords in C#

This example explains how you can protect a C# application from SQL injection attacks. The techniques can also protect other kinds of programs such as web applications.

In 2014 Russian hackers stole more than 1.6 billion (yes, that's 1,600,000,000) user names and passwords from the internet. For example, see the article Over 1.6B passwords stolen by Russian crime gang. The attack was in part an SQL injection attack. Those kinds of attacks are nothing new, so there's really no excuse for allowing this type of attack to succeed. The problem is that those running the attacked web sites didn't know how to defend themselves.

In this post, I'll describe several lessons that you should take from this security breach.

• What is an SQL injection attack?
• How can you protect a C# program from an SQL injection attack?
• How can you protect a database from password grabbing attacks?
• How can you protect yourself on the internet?
• What are the lessons to be learned?

What is an SQL injection attack?

In an SQL injection attack, a hacker enters fragments of SQL code in a text box to try to trick your program into doing something that it shouldn't, such as allowing the attacker to log in to the program without a valid password or fetching data from a password table.

How might this work? The example program uses an Employees table to hold user names and passwords. To log in to the application, you type your user name and password into text boxes. When you click the Query button, the program executes the following code.

// Log in by using a composed query. private void btnQuery_Click(object sender, EventArgs e) { string user_name = txtuser name.Text; string password = txtPassword.Text; string query = "SELECT COUNT (*) FROM Employees WHERE " + "user name='" + user_name + "' AND Password='" + password + "'"; txtQuery.Text = query; OleDbCommand cmd = new OleDbCommand(query, Conn); Conn.Open(); int count = 0; try { count = (int)cmd.ExecuteScalar(); } catch (Exception) { count = 0; } Conn.Close(); if (count != 0) { MessageBox.Show("Welcome " + txtuser name.Text); } else { MessageBox.Show("Invalid User Name/Password"); } }

This code gets the user name and password you entered, and uses them to compose a query of the form:

SELECT COUNT (*) FROM Employees WHERE user name='RodStephens' AND Password='Secret'

The code creates an OleDbCommand object to execute the query on the OleDbConnection object named Conn. (Conn is initialized in the form's Load event handler. Download the example to see the details.) The program calls the command's ExecuteScalar method, which executes a query that returns a single value, in this case the number of records selected.

If you have entered a correct user name and password, then this query should return 1 and the code allows you to log in.

Now suppose you enter the following weird values for the user name and password:

BadGuy AAA' OR 'X'='X

When the program composes its SQL query, you get this:

SELECT COUNT (*) FROM Employees WHERE user name='BadGuy' AND Password='AAA' OR 'X'='X'

Here the clause 'X'='X' is always true. Because that clause is added to the query by the OR keyword, the WHERE clause is always true. The WHERE clause selects every record in the Employees table. The number of records selected isn't 0 so the program allows BadGuy to log in.

How can you protect a C# program from an SQL injection attack?

There are several ways you can make SQL injection harder. First, you can strip any quote characters from the user name and password. If you click the program's Remove Quotes button, the following code executes.

// Use a query removing quotes within the fields. private void btnRemoveQuotes_Click(object sender, EventArgs e) { string user_name = txtuser name.Text.Replace("'", ""); user_name = user_name.Replace("\"", ""); string password = txtPassword.Text.Replace("'", ""); password = password.Replace("\"", ""); string query = "SELECT COUNT (*) FROM Employees WHERE " + "user name='" + user_name + "' AND Password='" + password + "'"; ... }

This code gets the user name and password and removes single and double quotes from them. It then uses the new values to build its query and executes the query as before. Now if you enter the previous user name and password, the program generates the following query:

SELECT COUNT (*) FROM Employees WHERE user name='BadGuy' AND Password='AAA OR X=X'

Unless there actually is a user with name "BadGuy" and password "AAA OR X=X," this query won't return any records.

One drawback to this approach is that it prevents users from including quote characters in user names and passwords. That's probably not the end of the world, but it's not really necessary.

This method also feels a little shaky. What if there's some other weird kind of SQL fragment you could enter to mess with the query? Perhaps with some other kind of delimiter?

If you click the example's Parameters button, the program executes the following code, which uses a third (and IMHO the best) approach.

// Use command parameters. private void btnParameters_Click(object sender, EventArgs e) { string user_name = txtuser name.Text; string password = txtPassword.Text; string query = "SELECT user name, Password FROM Employees " + "WHERE user name=? AND Password=?"; txtQuery.Clear(); OleDbCommand cmd = new OleDbCommand(query, Conn); cmd.Parameters.AddWithValue("user name", user_name); cmd.Parameters.AddWithValue("Password", password); Conn.Open(); bool login_ok = false; try { // Execute the command. OleDbDataReader reader = cmd.ExecuteReader(); // Read the first record. if (reader.Read()) { // Make sure the user name and password match. if ((reader.GetValue(0).ToString() == user_name) && (reader.GetValue(1).ToString() == password)) login_ok = true; // Make sure there's only one matching record. if (reader.Read()) login_ok = false; } } catch (Exception) { login_ok = false; } Conn.Close(); if (login_ok) { MessageBox.Show("Welcome " + txtuser name.Text); } else { MessageBox.Show("Invalid User Name/Password"); } }

This code gets the user name and password. It creates a query that uses question marks in place of the fields' values. The code then creates an OleDbCommand object to execute the query on the connection Conn.

Next the code uses the command object's AddWithValue method to add parameters to the command. They tell the command what values to substitute in for the question marks.

Now when the program executes the query, it doesn't need to worry about delimiters so the user-entered values can't confuse it. The database engine knows what values are supposed to be the field values so it doesn't need delimiters and it won't confuse parts of the values with SQL keywords such as = and OR.

The example program performs two other tests to make sure it isn't getting somehow confused. First it selects the values in the record not just a count of matching records. It then compares the retrieved user name and password with the values entered by the user. If the user somehow fooled the program, for example with a weird password like "AAA OR X=X," the retrieved values won't match what the user entered so the log in will fail.

Second, the program checks that exactly 1 row is returned. In the earlier versions, the password "AAA OR X=X" caused the query to match every record in the Employees table. That's obviously a mistake, but those approaches didn't check for this situation.

In fact, either of these tests would make the first two approaches work. The third approach is still better because the database engine knows that the values entered by the user are data and not part of the SQL statement.

Note that this example just spoofs a program's log in procedure. If you're not careful, depending on how your program composes its queries, BadGuy could make your program execute other SQL commands such as DELETE * FROM Employees or DROP TABLE Employees. Those could completely trash your database.

It could also make the program execute SQL SELECT statements to return every record from the Employees table. My guess is that's what the Russian hackers did.

(By the way, the database included in this example has a single record with user name "RodStephens" and password "Secret." All three methods let you log in with those values.)

How can you protect a database from password grabbing attacks?

One of the reasons the web sites attacked by the Russian hackers were vulnerable is that they stored user names and passwords in the database. This has the (small) advantage that the web site can email you your password if you forget it. It has the (enormous) disadvantage that someone can steal the user names and passwords. That's bad for your web site, but as I'll explain in the next section, it's even worse for your visitors.

An alternative is to store user names and encrypted passwords. When the user wants to log in, the program encrypts the entered password and sees if it matches the value stored in the database. The unencrypted password isn't stored anywhere in the database so hackers can't steal it even if they have full access to your whole database. (Of course that would cause other problems.)

This technique is much safer than simply storing user names and passwords, although it means you can't tell a user his password if he forgets it. Instead you must reset the password to a new value.

How can you protect yourself on the internet?

One huge problem with the internet is that people tend to use the same user names and passwords for lots of different sites. That means if one site is compromised, then many others are also.

Thta means even if you follow safe data handling practices and secure the passwords in your application and on your web site, they may still not be safe because some other web site didn't do the same! Suppose you have a visitor with user name "Clever" and password "Password." Now suppose they use that same password on another site and someone steals it. Now your web site is in danger, too.

There isn't a lot you can do about that. You can force users to follow all sorts of weird rules for picking passwords, but that will only annoy users and make them think of ways to circumvent your rules. In the end, a user's password is only as good as the effort they're willing to put into it.

However, you can make your own password use much safer by using different, non-obvious passwords for each web site. Keeping track of all those passwords is a huge hassle, so you should get (or build) a password manager to keep track of them for you.

What are the lessons to be learned?

There are several lessons to be learned here.

• Use parameters to query the database instead of building a SQL query string.
• More generally, never include text entered by the user in a SQL statement. Use parameters instead.
• Never execute user-entered queries unless you trust your users completely.
• Store passwords encrypted, not in plaintext. Then even if hackers somehow gets hold of your password data, it won't do them any good.
• Use different, non-obvious passwords for each web site.
• Use a password manager so you don't need to remember your passwords.

Download the example to experiment with it and to see additional details.