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.
InsertTitle(textBoxId.Text, textBoxTitle.Text,comboType.Text,
numberBoxPubId.Value,numberBoxPrice.Value,
numberBoxAdvance.Value,numberBoxYTDSales.Value,
textBoxNotes.Text,datePickerPubDate.Value);
}
privatevoid InsertTitle(string id,stringtitle, stringtype,
intpubId,double price,doubleadvance, intytd_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:
[ 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.
intpubId,double price,doubleadvance, intytd_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.
intpubId,double price,doubleadvance, intytd_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:
[ 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:
[ ytd_sales ] , [ notes ] , [ pubdate ] )
values ( ? , ? , ? , ? , ? , ? , ? , ? , ? )
The MySqlInsertStatement would create the following CommandText for a Microsoft.Data.Odbc.OdbcCommand:
`ytd_sales`, `notes`, `pubdate`)
values(?,?,?, ?,?,?,?, ?,?)
The OracleInsertStatement will create the following CommandText for a System.Data.OracleClient.OracleCommand:
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:
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
- https://www.mcpmag.com/backissues/features/article.asp?EditorialsID=324
- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetch12.asp
- https://www.devx.com/codemag/Article/10290
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
DownloadAll postings/content on this blog are provided "AS IS" with no warranties, and confer no rights. All entries in this blog are my opinion and don't necessarily reflect the opinion of my employer or sponsors. The content on this site is licensed under a Creative Commons Attribution By license.
Blog comments
0 responses