Parameterized Query and SQL Injection Attacks

What is SQL Injection Attacks?

 

An SQL injection attack targets interactive Web applications that employ database services. These applications accept user inputs and use them to form SQL statements at runtime.

 

During an SQL injection attack, an attacker might provide malicious SQL query segments as user input which could result in a different database request.

 

SQL injection attacks are used to steal information from a database from which the data would normally not be available and/or to gain access to an organization's host computers through the computer that is hosting the database.

 

By using SQL injection attacks, an attacker could thus obtain and/or modify confidential/sensitive information. An attacker could even use SQL injection vulnerability as a rudimentary IP/Port scanner of the internal corporate network.

 

As name suggest we inject SQL, which can be relatively dangerous for the database.

 

Example:

 

SQL command executed at runtime:

 

SELECT Name, Address, DOb, Fees FROM testpuran WHERE Rollno= ''1; DROP TABLE testpuran"

 

The above SQL command, which will be executed at backend and will drop the table, which is having an SQL injection attack code.

 

The following code will explain you about SQL Injection Attack:

 

Open new windows based project as File->New->Parameterized_Query

Add a new item application configuration file as shown below from Project -> Add New Item

 

The code for the application configuration file:

 

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <connectionStrings>

    <add name="constr" connectionString="initial catalog=puran; data source=MCN002; integrated security=sspi"/>

   </connectionStrings>

</configuration>

                  

Note: In the above code I have made a connection using windows authentication.

 

Add the necessary reference file from the solution explorer, Reference as below:


 

Add a DataGrid control, textbox and a button in your form view design as in the below figure:

 

Double click on your button and type the following code as:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.Configuration;

 

namespace Parameterized_query

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

 

        SqlDataAdapter da;

        DataSet ds;

 

        private void button1_Click(object sender, EventArgs e)

        {

            da = new SqlDataAdapter("select * from testpuran where rollno="+ textBox1.Text, ConfigurationManager.ConnectionStrings["constr"].ConnectionString);

            ds = new DataSet();

            da.Fill(ds);

            dataGrid1.DataSource = ds.Tables[0];

        }

    }

}

Output of the above code

 

Code explanation:

 

In the above code we have not used parameterized query, which is not validated, so there will be a SQL Injection attack.

 

How to Prevent SQL Injection?

 

To protect against SQL injection, user input must not directly be embedded in SQL statements. Instead, user input must be escaped, filtered, or parameterized statements must be used.

 

Parameterized Query

 

Parameterized query is used to solve SQL injection attack as it pass values as constant at backend.

 

Limit the length of user input

 

It's no good having a text box on a form that can accept 50 characters if the field you will comparing it against can only accept 10. By keeping all text boxes and form fields as short as possible, you're taking away the number of characters that can be used to formulate an SQL injection attack.

 

You can validate your textbox to prevent SQL Injection Attack.

 

Update the previous code with the following code, which is having a parameterized query to prevent SQL Injection Attack.

 

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.Configuration;

 

namespace Parameterized_query

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

 

        SqlDataAdapter da;

        DataSet ds;

 

        private void button1_Click(object sender, EventArgs e)

        {

            da = new SqlDataAdapter("select * from testpuran where rollno=@rol", ConfigurationManager.ConnectionStrings["constr"].ConnectionString);

            da.SelectCommand.Parameters.Add(new SqlParameter("@rol", SqlDbType.Int));

            da.SelectCommand.Parameters["@rol"].Value = textBox1.Text.ToString();

            ds = new DataSet();

            da.Fill(ds);

            dataGrid1.DataSource = ds.Tables[0];

        }

    }

}

 

Code explanation:

 

If you test the above code in correct input it will give proper output but if you give SQL injection attack code it will not work as we have used parameterized query.

 

Conclusion

 

After reading this article you now know about SQL injection attacks. I have explained SQL injection attacks with Microsoft SQL Server only in this article.

 

No database is safe. SQL injection attacks can also occur on MySQL and Oracle database servers and others.

 

Your feedback and constructive contributions are welcome.  Please feel free to contact me for feedback or comments you may have about this article.