Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Preventing SQL injection attacks

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.

Getting ready

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.

How to do it…

For this recipe, I am using SQL Server 2012. The concept is the same if you are using an older version of SQL Server. After you have created the CookbookDB database, you will see that there is a table called UserDisplayData under the Tables folder:
The 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:
We need to create a stored procedure to select data from this table for a specific ID (user ID). Click on the Programmability node to expand it:
Next, right-click on the Stored Procedures node and select New Stored Procedure… from the context menu:

SQL Server will create the following stored procedure template for you. This template consists of a section where you can comment on the particular stored procedure, as well as a section to add parameters you might need, and obviously a section that you need to add the actual SQL statement to:

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

Give the stored procedure a suitable name that will describe the action or intent of the stored procedure:
```
CREATE PROCEDURE cb_ReadCurrentUserDisplayData
```
Note
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.
Define a parameter for this stored procedure. By doing this, you are telling the database that when this stored procedure is called, it will pass through a value of type integer that is stored in a parameter caller @userID:
```
@userID INT
```
You now define the SQL statement to be used by this stored procedure. We are just going to do a straightforward SELECT statement:
```
SELECT
   Firstname, Lastname, Displayname
FROM
   dbo.UserDisplayData
WHERE
   ID = @userID
```
Note
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.
When you have completed adding the code to your stored procedure, it will look like this:
To create the stored procedure, you need to click on the Execute button. Be certain that you have the correct database selected when clicking on the Execute button:
The stored procedure will then be created under the Stored Procedures node in SQL Server:
We have now got to halfway through this task. It is time to construct the code that we will use in our application to query the database. We will be adding this code directly to the 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#.
To start, add the following using statement to the top of your console application:
```
using System.Data.SqlClient;
```

We then add the variables to contain the credentials we need to log on to the server:

int intUserID = 1;
int cmdTimeout = 15;
string server = "DIRK";
string db = "CookbookDB";
string uid = "dirk";
string password = "uR^GP2ABG19@!R";

We now use 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);

Note

For more on SecureString, see the Using SecureString in code recipe of this chapter.

We now create a 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();

Inside the 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;
```

After we have added our parameter with its value to the 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"]);

After you have added all the code to your console application, the correct completed code will look as follows:

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();

Run your console application and you will see the display name output to the screen:

How it works…

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for Preventing SQL injection attacks

Create new playlist

Sign In

Sign Up

Preventing SQL injection attacks

Getting ready

How to do it…

Note

Note

Note

How it works…

Table of Contents for
Preventing SQL injection attacks