Effective Strategies to Prevent SQL Injection

Introduction about SQL Injection

An injection attack known as SQL Injection (SQLi) enables the execution of malicious SQL statements. These commands manage a database server that sits in front of a web application. SQL Injection flaws allow attackers to get around application security measures. The entire content of a SQL database can be retrieved by getting around authentication and authorization of a web page or web application. They can also add, modify, and delete records in the database using SQL Injection.

Any website or web application that makes use of a SQL database, such as MySQL, Oracle, SQL Server, or another one, may be vulnerable to a SQL Injection flaw. Your sensitive data, including customer information, personal information, trade secrets, intellectual property, and more, could be accessed by criminals without your permission. One of the oldest, most common, and most harmful web application vulnerabilities is SQL Injection attacks. Injections are ranked as the top threat to web application security by the OWASP organization (Open Web Application Security Project) in their list of the top 10 threats for 2017.

The Methods Used in SQL Injection Attacks

Understanding how these attacks happen can help lay the groundwork for their prevention.

Attackers search the web page or web application for weak user input to launch SQL Injection attacks. A landing page or web form that takes this data and enters it directly into the database could serve as an illustration of this. Here, the process is what is vulnerable.

The malicious component of the process is the attacker, who can then produce false input content. After the attacker sends this content, the database executes SQL commands, which allows for a range of malicious activity.

The query language known as SQL was created to manage data kept in relational databases. In some circumstances, you can also execute operating system commands using SQL commands. A successful SQL Injection attack can have very negative effects on either a SQL query or SQL statement.

Tips to Prevent SQL Injection

1. ADO.NET SQL injection attack prevention

There are two standard ways to execute a SQL request to the database, as is customary for the most significant and widely used technologies. The first and most straightforward method is to use either manually forged SQL requests or an ORM. We’ll see an illustration of what potentially dangerous ADO.NET-based code might resemble in the first section of this post.

SqlConnection con = new SqlConnection(…); 
    string qry="select * from users where username='"+User.UserName+"'and password='"+User.Password+"' "; 
adpt = new SqlDataAdapter(qry,con); 
dt = new DataTable(); 
adpt.Fill(dt); 
if (dt.Rows.Count >= 1) 
{ 
        Response.Redirect("dashboard.aspx"); 
}

You can see that we are directly injecting the user’s data in a valid SQL request by looking at this source code. By doing this, the attacker has the chance to insert a malicious SQL request into this valid one.

You must therefore filter the user data before including it in the sql request using the SqlParameters class in order to fix this vulnerability. Here is an illustration of how to correct this code:

SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;" + "AttachDbFilename=|DataDirectory|UserDetails.mdf;Integrated Security=True;User Instance=True");
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM users where Username=@Username and Password=@Password", conn);
SqlParameter p1 = new SqlParameter();
P1.ParameterName = "@Username";
P1.Value = User.Username;
cmd.Parameters.Add(p1);
SqlParameter p2 = new SqlParameter();
p2.ParameterName = "@Password";
p2.Value = User.Password;
cmd.Parameters.Add(p2);
SqlDataReader rdr = cmd.ExecuteReader();

Note:

I’m fairly certain that using this method to send multiple parameters at once is a little bit challenging. I don’t typically use this method of database communication, but if you do and you aren’t planning to change it, here is a workaround:

cmd.Parameters.AddRange(new []
{
        new SqlParameter("@variable1", myValue1),
        new SqlParameter("@variable2", myValue2),
new SqlParameter("@variable3", myValue3),
});

2. ADO.NET Dapper SQL injection attack prevention

Dapper is one of the most well-known micro ORMs for.NET. This ORM provides ADO.NET with a set of methods to query data and turn the results into typed objects. Here is an illustration of a Dapper-using vulnerable source.

const string sql = "select * from users where username = "+ User.Username+" AND password="+ User.Password;
var userInfos = await conn.ExecuteScalarAsync<string>(sql);
Console.WriteLine(userInfos);

As usual, prepared statements must be used to fix this kind of weak code. Here’s an illustration of such use:

const string sql = "select * from users where username = @username AND password=@password";
var params = new { username = User.Username, password = User.Password }
conn.Query<Users>(sql,params)

3. ASP.NET NHibernate attack prevention

For the.NET framework, NHibernate is an established open source object-relational mapper. It is actively developed, fully functional, and utilized in countless prosperous projects. The ORM architecture and mechanism typically protect it from SQL injections. However, improper use of some permitted ORM methods weakens it and leaves it open to SQL injection.

Using the NHibernate ASP.NET ORM, consider the following example of vulnerable code:

string userName = ctx.GetAuthenticatedUserName();
string query = “SELECT * FROM users WHERE username = ‘” + userName + “‘ AND password = ‘” + password + “‘”;
List items = sess.CreateSQLQuery(query);

If you look closely at this example, you will notice that some user inputs without any filtering are inserted into the otherwise legitimate SQL request. Even if you are using an ORM, this makes the entire application susceptible to SQL injection.

Now, in order to fix this code, you must first create a valid request before injecting the user inputs securely. To fix this source code, follow this example:

string userName = ctx.GetAuthenticatedUserName();
string query = “SELECT * FROM users WHERE username = :username AND password = :password”;
List items = sess.CreateSQLQuery(query)
                 .SetParameter(“username”, password)
                 .SetParameter(“password”, username);

Conclusion

Above article are few ways to prevent SQL injection. If there is other methods to prevent SQL injection, please feel free to comment below. If you find this article helpful, you can always share this article.