SQL-strings considered harmful
By Fons Sonnemans (10 March 2003)
Download
SqlInsert.zip
Introduction
Did you know that any malicious user can corrupt your database by injecting harmful
SQL strings? To prevent SQL injection, you can use the parameters collections when
building SQL strings. However, I developed a more sophisticated method to construct
SQL statements from user's input and execute them safely. In this article I describe
how to write and execute SQL statements by using objects instead of SQL strings.
These objects also address specific SQL statement syntax issues on different RDBMS:
they enable you to write generic and RDBMS independent code.
SQL Inject
An SQL statement that is to be executed by SQL Server is in most applications constructed
using an SQL string (or StringBuilder) and executed by means of a command object.
An SQL string can contain two or more SQL statements (each statement separated by
a semi colon) that all will be executed by SQL Server.
If you build SQL strings using unfiltered input, your application may be subject
to malicious user input (remember, never trust user input). The risk is that when
you insert user input into a string that becomes an executable statement, a malicious
user can append SQL commands to your intended SQL statements by using escape characters.
Example: consider a form in which a record has to be added to a table called Titles.
The user can type field values like title (name), publisher-ID, price etc. in textboxes
on the form. On OK click the InsertTitle() method is called using these input values
as arguments.
private void
buttonOK_Click(object
sender, System.EventArgs e)
{
InsertTitle(textBoxId.Text,
textBoxTitle.Text,
comboType.Text,
numberBoxPubId.Value, numberBoxPrice.Value,
numberBoxAdvance.Value, numberBoxYTDSales.Value,
textBoxNotes.Text, datePickerPubDate.Value);
}
private void InsertTitle(string
id, string
title,
string type,
int pubId, double
price, double
advance,
int ytd_sales,
string
notes, DateTime pubdate) {
StringBuilder sql
= new StringBuilder("insert into titles ");
sql.Append("(title_id, title, type, pub_id, price,
advance, ");
sql.Append("ytd_sales, notes, pubdate)");
sql.Append(" values (");
sql.Append("'").Append(id).Append("' ,");
sql.Append("'").Append(title).Append("' ,");
sql.Append("'").Append(type).Append("' ,");
sql.Append(pubId).Append("
,");
sql.Append(price).Append("
,");
sql.Append(advance).Append("
,");
sql.Append("'").Append(notes).Append("' ,");
sql.Append(string.Format("{0:MM/dd/yyyy})",
pubdate));
SqlCommand c =
new SqlCommand(sql.ToString(), MyConnection);
c.ExecuteNonQuery();
}
In the example, the SQL statement is constructed from a simple string concatenation.
The assumption is that some fields of the form are alphanumeric, and thus must
be surrounded by single quotes. Since SQL sees the single quotes as a string delimiter,
all a hacker needs to do is insert an extra quote, followed by any SQL code into
the textBoxNotes. For example, say the value:
', null); delete from titles --
was entered. The InsertTitle() method would build the SQL statement to look
something like this:
insert into titles (title_id, title,
type,
pub_id, price, advance,
[ytd_sales],
[notes], [pubdate])
values ('FS1234', 'dummy',
'business',
1, 1,
1,
1, '',
null);
delete from titles --,
22/04/2000)
This would cause SQL to execute the INSERT statement, possibly returning an error
because the pubdate field was NULL (if the database were so programmed). The
server would then execute the DELETE statement, deleting all records from the
table. The harm is done!
The Solution
To prevent SQL injection, you can use the parameters collection when building SQL
strings. No matter what a malicious user includes as input, the input is treated
as a literal.
private void InsertTitle(string
id, string
title,
string type,
int pubId, double
price, double
advance,
int ytd_sales,
string
notes, DateTime pubdate) {
StringBuilder sql
= new StringBuilder("insert into titles ");
sql.Append("(title_id, title, type, pub_id, price,
advance, ");
sql.Append("ytd_sales, notes, pubdate)");
sql.Append(" values (@par0, @par1, @par2, @par3,
@par4, @par5, ");
sql.Append("@par6, @par7, @par8)");
SqlCommand c =
new SqlCommand(sql.ToString(), MyConnection);
c.Parameters.Add(new SqlParameter("par0",
id));
c.Parameters.Add(new SqlParameter("par1",
title));
c.Parameters.Add(new SqlParameter("par2",
type));
c.Parameters.Add(new SqlParameter("par3",
pubId));
c.Parameters.Add(new SqlParameter("par4",
price));
c.Parameters.Add(new SqlParameter("par5",
advance));
c.Parameters.Add(new SqlParameter("par6",
ytd_sales));
c.Parameters.Add(new SqlParameter("par7",
notes));
c.Parameters.Add(new SqlParameter("par8",
pubdate));
c.ExecuteNonQuery();
}
To mark a name as a parameter and not as a string of literal characters, you place
a prefix in front of the parameter name (and, as an option, a suffix after it).
For example, parameter marker characters might be "@" (SqlServer), ":" (Oracle),
or "%". Typically, you use the parameter marker character recognized for your database.
Use can used unnamed parameters when your database doesn't support named parameters.
An unnamed parameter is a question mark (?) that you put anywhere in the query that
you want to prompt for or substitute a literal value.
InsertStatement classes
To make parameter usage easier you should use Objects and not SQL strings to build
your SQL statements. The InsertStatement classes are used to build an IDbCommand.
They create the CommandText and the Parameters collection. I have currently
four implementations for SqlServer, MS Access, MySql and Oracle. It enabled
me to support also RDBMS specific syntax for: keywords, parameters, table-names
& column-names, (outer)joins and aliasses.
private void InsertTitle(string
id, string
title,
string type,
int pubId, double
price, double
advance,
int ytd_sales,
string
notes, DateTime pubdate) {
SqlInsertStatement i =
new SqlInsertStatement();
i.TableName
= "titles";
i.Values.Add("title_id",
new Constant(id));
i.Values.Add("title",
new Constant(title));
i.Values.Add("type",
new Constant(type));
i.Values.Add("pub_id",
new Constant(pubId));
i.Values.Add("price",
new Constant(price));
i.Values.Add("advance",
new Constant(advance));
i.Values.Add("ytd_sales",
new Constant(ytd_sales));
i.Values.Add("notes",
new Constant(notes));
i.Values.Add("pubdate",
new Constant(pubdate));
SqlCommand c =
(SqlCommand)i.CreateCommand();
c.Connection
= MyConnection;
c.ExecuteNonQuery();
}
The SqlInsertStatement will create the following CommandText for a System.Data.SqlClient.SqlCommand:
insert into [titles]
([title_id],
[title], [type],
[pub_id], [price],
[advance],
[ytd_sales],
[notes], [pubdate])
values (@par0, @par1,
@par2,
@par3, @par4, @par5,
@par6,
@par7, @par8)
The AccesInsertStatement would create the following CommandText for a System.Data.OleDb.OleDbCommand:
insert into [titles]
([title_id],
[title], [type],
[pub_id], [price],
[advance],
[ytd_sales],
[notes], [pubdate])
values (?, ?,
?,
?, ?, ?,
?,
?, ?)
The MySqlInsertStatement would create the following CommandText for a Microsoft.Data.Odbc.OdbcCommand:
insert into `titles` (`title_id`, `title`,
`type`, `pub_id`, `price`,
`advance`,
`ytd_sales`, `notes`, `pubdate`)
values (?, ?,
?,
?, ?, ?,
?,
?, ?)
The OracleInsertStatement will create the following CommandText for a System.Data.OracleClient.OracleCommand:
INSERT INTO titles (title_id,
title, type, pub_id,
price,
advance,
ytd_sales, notes,
pubdate)
VALUES (:par0,
:par1,
:par2,
:par3,
:par4,
:par5,
:par6,
:par7,
:par8)
The InsertStatement and ValuesClause use arrays of tokens to store all information.
The CreateCommand() method uses a CommandBuilder to create the CommandText and Parameters.
These CommandBuilders will iterate through the arrays and translate each token (keyword,
literal, constant, expression, etc) to the RDBMS specific implementation.
Select, Update and Delete classes
You can use the same solution for the Select, Update and Delete statements. They
are a lot more difficult but it is really worth the effort, especially when you
want to create generic, RDBMS independent components. I ended up with the following
model:
The SelectStatementBase class has for each clause an association with a Clause object.
Example with an outerjoin and a like in the where clause:
SelectStatementBase s = new
SqlSelectStatement();
s.Select.AddAstrix();
s.From.Add("sales",
"s");
s.From.Add(new Join("stores",
"s.stor_id", "stores.stor_id", JoinTypes.Left));
s.Where.And(new ConditionLike("title",
new Constant("%book%")));
s.OrderBy.Add("ord_date",
true);
s.OrderBy.Add("qty");
Update and Delete model:
Additional Information
Conclusion
Writing SQL statements using objects instead of strings made my life easier. I hope
you will use my classes which use the Parameters so SQL injection is prevented.
The download contains the C# source code of the Insert classes and a sample project.
Any suggestions and feedback for improving this article is most welcome. Send your
suggestions and feedback to Fons.Sonnemans@reflectionit.nl