SQL injection attacks are a very real problem. There are too many applications that still make themselves vulnerable to this kind of attack. If you develop a web application or website, you should be vigilant of bad database operations. Vulnerable in-line SQL exposes the database to a SQL injection attack. A SQL injection attack is where an attacker modifies SQL statements via a web form input box to produce a different result than originally intended. This is usually attempted on a form where the web application is supposed to access the database to authenticate the user login. By not sanitizing the user input, you are exposing your data to exploits such as this.
The accepted solution to mitigate SQL injection attacks is to create a parametrized stored procedure and call that from your code.
You need to create the CookbookDB
database in your SQL Server before continuing this recipe. You will find the script in the _database scripts
folder in the accompanying source code.
CookbookDB
database, you will see that there is a table called UserDisplayData
under the Tables
folder:UserDisplayData
table is simply used to illustrate the concept of querying using a parameterized stored procedure. It would not have any real benefit in a production database, because it only returns a screen name:Programmability
node to expand it:Stored Procedures
node and select New Stored Procedure… from the context menu:SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> -- Add the parameters for the stored procedure here <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0> AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> END GO
CREATE PROCEDURE cb_ReadCurrentUserDisplayData
There are many people that do prefix their stored procedures, and I'm one of those. I like to keep my stored procedures grouped. I therefore name my stored procedures in the format [prefix]_[tablename_or_module]_[stored_procedure_action]. Having said that, I generally avoid using sp_
as a prefix to my stored procedures. There are a lot of opinions on the Internet as to why this is a bad idea. It is generally believed that using sp_
as a stored procedure prefix impacts on performance because it is used as the stored procedure prefix in the master database.
For the purposes of this recipe, I have just kept to a simple name for the stored procedure.
@userID
:@userID INT
SELECT
statement:SELECT Firstname, Lastname, Displayname FROM dbo.UserDisplayData WHERE ID = @userID
You will notice that my SELECT
statement contains the specific column names instead of a SELECT * FROM
. Doing a SELECT *
is considered bad practice. You would usually not want to return all the column values from a table. If you want all the column values, then it is better to explicitly list the columns by name instead of just getting all.
Using SELECT *
returns unnecessary columns and increases the overhead on the server. This does make a difference in the bigger scheme of things, especially when the database starts getting a lot of traffic.
The thought of having to type out the column names for a large table is definitely not something I would look forward to. You can however use the following tricks to make it easy for you to add the column names to your SQL SELECT
statement. You can right-click on the database table and select Script Table As to create one of several SQL statements. Secondly, you can expand the Table
node and expand the table you wish to write the statement for. You will then see a node called Columns
. Drag the Columns
node onto the query editor. That will insert all the column names into the query editor for you.
Stored Procedures
node in SQL Server:Program.cs
file of your console application. While this code isn't considered best practice (hardcoding the server credentials), it serves merely to illustrate the concept of calling a parameterized stored procedure from C#.using
statement to the top of your console application:using System.Data.SqlClient;
int intUserID = 1; int cmdTimeout = 15; string server = "DIRK"; string db = "CookbookDB"; string uid = "dirk"; string password = "uR^GP2ABG19@!R";
SecureString
to store the password and add it to a SqlCredential
object:SecureString secpw = new SecureString(); if (password.Length > 0) { foreach (var c in password.ToCharArray()) secpw.AppendChar(c); } secpw.MakeReadOnly(); string dbConn = $"Data Source={server};Initial Catalog={db};"; 6SqlCredential cred = new SqlCredential(uid, secpw);
SqlConnection
object inside a using
statement. This ensures that the SQL connection is closed when the using
statement moves out of scope:using (SqlConnection conn = new SqlConnection(dbConn, cred)) { try { } catch (Exception ex) { Console.WriteLine(ex.Message); } } Console.ReadLine();
try
, add the following code to open the connection string and create a SqlCommand
object that takes the open connection and name of the stored procedure as parameters. You can use the shortcut method of creating the actual SQL parameter to pass to the stored procedure:cmd.Parameters.Add("userID", SqlDbType.Int).Value = intUserID;
Because I'm just passing a parameter of type integer to the stored procedure, I'm not defining a length for this parameter:
If, however, you ever need to define a parameter of type VarChar(MAX)
, you would need to define the size of the parameter type by adding -1
. Let's say, for example you need to store a student's essay in the database, the code would then look as follows for the VarChar(MAX)
:
cmd.Parameters.Add("essay", SqlDbType.VarChar, -1).Value = essayValue;
SqlCommand
object, we specify a timeout value, execute the SqlDataReader
, and load it into a DataTable
. The value is then output to the console application:conn.Open(); SqlCommand cmd = new SqlCommand("cb_ReadCurrentUserDisplayData", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("userID", SqlDbType.Int).Value = intUserID; cmd.CommandTimeout = cmdTimeout; var returnData = cmd.ExecuteReader(); var dtData = new DataTable(); dtData.Load(returnData); if (dtData.Rows.Count != 0) Console.WriteLine(dtData.Rows[0]["Displayname"]);
int intUserID = 1; int cmdTimeout = 15; string server = "DIRK"; string db = "CookbookDB"; string uid = "dirk"; string password = "uR^GP2ABG19@!R"; SecureString secpw = new SecureString(); if (password.Length > 0) { foreach (var c in password.ToCharArray()) secpw.AppendChar(c); } secpw.MakeReadOnly(); string dbConn = $"Data Source={server};Initial Catalog={db};"; SqlCredential cred = new SqlCredential(uid, secpw); using (SqlConnection conn = new SqlConnection(dbConn, cred)) { try { conn.Open(); SqlCommand cmd = new SqlCommand("cb_ReadCurrentUserDisplayData", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("userID", SqlDbType.Int).Value = intUserID; cmd.CommandTimeout = cmdTimeout; var returnData = cmd.ExecuteReader(); var dtData = new DataTable(); dtData.Load(returnData); if (dtData.Rows.Count != 0) Console.WriteLine(dtData.Rows[0]["Displayname"]); } catch (Exception ex) { Console.WriteLine(ex.Message); } } Console.ReadLine();
By creating a parameterized SQL query, the compiler correctly substitutes the arguments before running the SQL statement against the database. It will prevent malicious data changing your SQL statement in order to exact a malicious result. This is because the SqlCommand
object does not directly insert the parameter values into the statement.
To sum it all up, using parameterized stored procedures means no more Little Bobby Tables.