Secure Your Data: Avoid SQL Injection Using C#

In this blog, you will learn about implementing security in the database against SQL Injection.

The database has become a part of almost every application. The data in it is very precious to admins and users of the application. So, when working with a database, one must make sure that the data of the users is secure.

For this purpose, I assume that you are using SQL Server management studio to query data from the database. For example, let’s say that you have a form in which people can fill in their names that you will insert into the People table. Your query will look something like this,

INSERT INTO People VALUES (‘Ali’, ‘Raza’, null)

Now, you could read the values that a user enters and then manually construct this string. But what would happen if a user enters the following for a middle name,

‘); DELETE FROM People

After executing this query, all of your data from the database will be deleted, which shows that data is not secured. This is called SQL Injection. For another example, if a user when working on ASP.NET makes a form to get user input, and a second person provides <h1> element in the name field instead of name, this will be saved into the database and when retrieved from database, it will be rendered as a h1 tag in the browser instead of name and this will create a big problem for the user.

To guard against SQL injection, you should never directly use user input in your SQL strings. Instead of manually building the correct SQL query, you can use parameterized SQL statements as shown in the code below:

String conString = @ " Data Source=.; Initial Catalog=People; Integrated Security=SSPI";

SqlConnection sc = new SqlConnection(conString);

Sc.Open();

String Query = " INSERT INTO People([FirstName], [LastName], [MiddleName]) VALUES (@firstName, @lastName, @middleName)";

SqlCommand sqlcommand = new SqlCommand(Query, sc);

sqlcommand.Parameters.AddWithValue("@firstName", "Ali");

sqlcommand.Parameters.AddWithValue("@lastName", "Raza");

sqlcommand.Parameters.AddWithValue("@middleName", "Little");


int numberOfInsertedRows = sqlcommand.ExecuteNonQuery();

Console.WriteLine("Inserted {0} rows", numberOfInsertedRows);

Sc.Close();

}

}

These parameterized queries can be used when running select, update, insert, and delete queries. Besides being much more secure, they also offer better performance.