Back in ’98 I was developing an extranet site for a local company when I realised that it would be open for exploit if somebody put single quotes in text fields. It was early in the development cycle so I fixed it and moved on, unable to find out how other people were avoiding the problem.
Updated October 2026: The core advice here still stands, parameterise everything and never build SQL by concatenating strings, but the login example compared a plain-text password column, which is something you should never do. I've marked the vulnerable code as what not to do, reworked the example to look up a salted password hash and verify it in code with a proper password-hashing function, and expanded the notes on least-privilege database accounts, stored procedures and ORMs.
It turned out many were not and it became a well-known exploit called SQL injection. Unfortunately there are many developers who don’t know or appreciate the problem, and it is this:
If you build SQL by appending strings and data without correct encoding your application can be exploited. These exploits can range from exposing sensitive information, through to modification and deletion of data.
This problem is very real and applies to:
- All SQL statements, not just SELECT
- All database systems, not just MS SQL or MySQL
- All programming environments, not just C#, PHP or ASP
- All data, most essentially that obtained from end-users, regardless of client-side checking
Let’s walk through an example and see how it works and what can be done to avoid it.
Example: User login
We have a user-name and password from a web form and want to get the user’s ID from the database, or nothing if it wasn’t valid. A naive developer might want to send something like this SQL statement to our database.
SELECT UserID FROM Users WHERE UserName='Bob' AND Password='test'
Don’t do this. Storing and comparing passwords in plain text is a serious problem all on its own (more on that below), but I’ve kept it here because it shows the injection attack so clearly.
And so a developer might do something like this (in C# using .NET) which is exactly what not to do;
var dr = connection.Execute("SELECT UserID FROM Users WHERE UserName='" + Request("UserName") + "' AND Password='" + Request("Password") + "'");
if (dr.Read()) userId = dr.GetInt32(dr.GetOrdinal("UserID"));
The problem here is that if there is a ‘ in the form fields it effectively breaks out of the selection criteria and allows the end user to add extra criteria or even commands to what we are sending to the database server. Should they enter the following into the password form field...
' OR ''='
Then our code above will send the following SQL to the database:
SELECT UserID FROM Users WHERE UserName='aaa' AND Password='' OR ''=''
Which will return every record in the database and our code will let him log in as the first user it finds, normally a developer or administrator account. Ouch!
Bad solution: Encode it yourself
One solution often adopted is to always ensure all string input has a single-quote replaced by two single-quotes, which is what SQL server expects if you really want to send it a single quote.
This solution fails in that it doesn’t handle numbers or dates and falls apart in that both numbers and dates are often regionally formatted. It’s also easy to miss a spot, and the OWASP SQL Injection Prevention Cheat Sheet strongly discourages hand-escaping because it can’t guarantee to stop every injection.
Good solution: Let the DB client encode it
A much better solution is to use your environment to perform all the proper encoding for you. As well as protecting you from such exploits you’ll also avoid localisation problems where the string representation of something on your client is interpreted differently in your database. This can be a real problem in the UK where dates formatted by a UK web-server are sent to a misconfigured SQL server expecting US formatting and the days and months become transposed without error.
These are parameterised queries (sometimes called prepared statements) and they keep the SQL and the data completely separate, so nothing a user types can change what the query does. Here the user name goes in as a parameter, and rather than comparing a plain-text password in SQL we fetch the stored salt and hash and verify the password in code:
var cmd = new SqlCommand("SELECT UserID, PasswordHash, PasswordSalt FROM Users WHERE UserName=@UserName", connection);
cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 255).Value = userName;
using var dr = cmd.ExecuteReader();
if (dr.Read())
{
var salt = (byte[])dr["PasswordSalt"];
var storedHash = (byte[])dr["PasswordHash"];
var attemptHash = Rfc2898DeriveBytes.Pbkdf2(password, salt, 600_000, HashAlgorithmName.SHA256, storedHash.Length);
if (CryptographicOperations.FixedTimeEquals(attemptHash, storedHash))
userId = dr.GetInt32(dr.GetOrdinal("UserID"));
}
(Rfc2898DeriveBytes.Pbkdf2 and CryptographicOperations.FixedTimeEquals are in System.Security.Cryptography on .NET 6 and later. In a real system you’d also store the iteration count alongside the hash so you can raise it over time, or better still let a library such as ASP.NET Core Identity’s PasswordHasher<TUser> manage the format for you.)
Okay there is no doubt this is a little longer but it takes care of all our encoding and localisation worries, and if for example you want to insert a lot of data into the database, creating the command and parameters once, then just setting the parameters for each insert (and executing it) will run faster than lots of string building inserts...
Moral of the story
For SQL that means never concatenating user data into a statement: use parameters every time, whether you’re writing raw ADO.NET, calling stored procedures or going through an ORM.
More generally, always encode data properly. If you can use the provided methods and functions to do so. If none are provided grab the specification and find out all the special characters used. Learn what encoding and escape sequences are used and apply them properly.
A few places where data should be encoded properly:
- HTML: Obviously < and > have special meanings and need to be escaped. ASP.NET controls will take care of this if you set the .Text or .InnerText properties but set .InnerHTML at your own peril. Old ASP has the Server.HTMLEncode() function.
- URL: A whole host of rules but the query string is often modified in code. Use URLEncode() or something similar especially if you want XHTML compliance too.
- XML: Again a whole host of rules for what is valid data. Either use an XML object to write out your data (MSXML, Xerces etc) or maybe even store it in
[[CDATAsections. - CSV: Even comma-separated value files have encoding rules. What do you need to do if text is going to have a ” in a field. What happens if a number contains a comma! Find out or use a well regarded library to do it for you.
Notes about the example
Never store or compare passwords in plain text. Each password should be stored only as a salted hash produced by a deliberately slow password-hashing function. The OWASP Password Storage Cheat Sheet recommends Argon2id first, then scrypt, with bcrypt for legacy systems and PBKDF2 where FIPS-140 compliance is required. Fast general-purpose hashes like SHA-256 or SHA-512 on their own are not suitable. Better still, use a well-tested library or framework (such as ASP.NET Core Identity) rather than rolling your own.
A better login system would also not allow the web server broad access to sensitive data such as the user table. Have the application connect with a least-privilege database account that can do only what it needs (and certainly never as sa or a db_owner), and consider restricting access to sensitive information through stored procedures or views that enforce those restrictions.
Stored procedures aren’t a silver bullet though. They’re only safe when you call them with parameters and they don’t build dynamic SQL from their inputs internally; a procedure that glues strings together and runs them with EXEC is just as injectable as the C# above.
Such a login system would therefore look up the user’s stored hash via a parameterised query or stored procedure, verify the password in code, log the attempt, and throttle or lock out the account after too many incorrect attempts.
If you’re using an ORM such as Entity Framework Core, its LINQ queries and the FromSql and ExecuteSql methods send your values as parameters for you, but the FromSqlRaw and ExecuteSqlRaw methods will happily let you concatenate user input straight back into the SQL, so the same rules apply there.
Finally, when you only need a single field back, it’s better achieved by using ExecuteScalar() and forgetting a data reader.
Microsoft have a developer how-to on injection. These days the OWASP SQL Injection Prevention Cheat Sheet is the place to start.
[)amien
0 responses