Solve SQL Injection Problem in C#

Introduction

SQL Injection is a combination of a SQL Query that can passed in from user input from your website and the execution of the query in your back-end database. In this article you will learn about one of the SQL Injection problems. I also have an example to better clarify my points about SQL Injection.

Suppose we have a table in a SQL database like:



I inserted a value into it, after inserting the value the table data looks like:

Now I try to update the name using the id column, so I write a simple query on a SQL query prompt.

"UPDATE emp SET name='sharad' where id=1;"

No problem, the result returned is:



But if you try to update or insert a name (like 'sharad's') using SQL query such as in the following query that updates the name on the behalf of id:

UPDATE emp set name='gupta's friend' WHERE id=1;

Then it will produce an error like:

To solve that problem you can easily write this query in the following style:

UPDATE emp set name ='sharad''s' where id=1;

Again, no problem, the result returned is:

So the query above is easily maintained with a SQL command prompt.

Now to move to Windows Forms application. Suppose we want to update the name, when the user inputs a name value inside the TextBox on the behalf of id columns of "emp" table. So in the Windows Forms application you cannot write an update command in the manner shown above, since you are taking input from the TextBox, so how to manage that value in C# code of a Windows Forms application?

First I write code for updating the emp table name column value on the behalf of the id column.

Note: It only works when you update the name like in the 'sharad or  'ravi' format.

con = new SqlConnection(constr);

            con.Open();

            cmd = new SqlCommand("update emp set name='"+textBox2.Text+"' where id="+textBox1.Text+"", con);

            cmd.ExecuteNonQuery();

            con.Close();

But when you update a name like sharad's or ravi's, then the code given above will produce an error.

Such as in the following image when you have pressed the update button.



The error image is:

To solve that problem, you can create a Stored Procedure, as in the following:

CREATE PROCEDURE test(@input1 varchar(40),@input2 varchar(5))

as

update emp set name= @input1 where id=@input2;

go

and use it in to your C# code.
 

using System;

using System.Windows.Forms;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

namespace SqlInjection

{

    public partial class Form1 : Form

    {

        SqlConnection con;

        SqlCommand cmd;

        DataTable dt;

        SqlDataReader dr;

        string constr = ConfigurationManager.ConnectionStrings["constr"].ToString();

        public Form1()

        {

            InitializeComponent();

        }

 

        private void button1_Click(object sender, EventArgs e)

        {

            con = new SqlConnection(constr);

            con.Open();

            cmd = new SqlCommand("test", con); // use stored procedure
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@input1",textBox2.Text));

            cmd.Parameters.Add(new SqlParameter("@input2",textBox1.Text));

            cmd.ExecuteNonQuery();

            con.Close();

        }

 

        private void button2_Click(object sender, EventArgs e)

        {

            con = new SqlConnection(constr);

            con.Open();

            cmd = new SqlCommand("select * from emp", con);

            dr = cmd.ExecuteReader();

            dt = new DataTable();

            dt.Load(dr);

            dataGridView1.DataSource = dt;

            con.Close();

 

        }

    }

}

The name has been successfully updated and the updated tables data looks like: