SQL injection attack prevention in JEE | Complete guide
For those who didn’t see the first part. This blog post is a part of a long series where I explain in detail how to prevent the SQL injection attack ...
For those who didn’t see the first and the second part. This blog post is a part of a long series where I explain in detail how to prevent and how to fix the SQL injection attack vulnerability in multiple web development technologies. The idea is to create the most detailed and the most complete guide on how to fix a SQL injection for all the web technologies
As I always say this guide is an open series of posts and this is the third one only. If you want me to add any technology, please just comment below or contact me, and I will be very happy to explain SQL injection prevention for that technology. This guide will analyze the following technologies:
The first post has focused on the PHP language and all its frameworks then we have discussed the prevention techniques in a JEE-based code. In this part of the series we will see how a vulnerable code to SQL injection attack may look like in an ASP.NET application and how can we fix it.
For those who do not know what is SQL injection attack, let me do a small introduction to explain this to them. SQL injection attacks happen when a bad user tries to inject a malicious SQL request into a legitimate request. The impact of SQL injection attack differs from a situation to another depending on multiple elements related to the app environment, and it can go from as “simple” as information leak to a full server control. I will not go deeper into the way an attacker could exploit this vulnerability or how we can discover it in blackbox tests as this is not the objective of this post. What we will see is how to discover it in source code and how to fix it.
As usual in the biggest and popular technologies, to perform an SQL request to the database we get two general ways. The first and direct one is by using the manually forged SQL requests or using an ORM. In this first part of this post, we will see an example of how a vulnerable ADO.NET-based code may look like.
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");
}
By analyzing this source code, you will see that we are injecting the user’s data directly in a legitimate SQL request. Doing so give the attack the possibility to inject a malicious SQL request in this legitimate one.
Therefore, to fix this vulnerability you will need to use the SqlParameters class to filter the user data before using them in the sql request. Here is an example of how to you can fix 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 am pretty sure that this technique is a little bit difficult to use when we need to send multiple parameters at once. I usually don’t use this technique to communicate with the database but if you are using it and you are not thinking about changing it, then here is a workaround:
cmd.Parameters.AddRange(new []
{
new SqlParameter("@variable1", myValue1),
new SqlParameter("@variable2", myValue2),
new SqlParameter("@variable3", myValue3),
});
One of the most known micro ORMs available for .NET, we have Dapper. This ORM, give a set of methods for ADO.NET to query data and convert the results into a typed object. Here is an example of a vulnerable source using Dapper.
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, to fix this kind of vulnerable code you will need to use the prepared statements. Here is an example 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)
NHibernate is a mature, open source object-relational mapper for the .NET framework. It’s actively developed, fully featured and used in thousands of successful projects. Usually the ORM architecture and mechanism make it immune against SQL injections. However, the bad use of some allowed methods given by the ORM it self weaken it and make it vulnerable to SQL injection.
Here is an example of a vulnerable code using the NHibernate ASP.NET ORM :
string userName = ctx.GetAuthenticatedUserName();
string query = “SELECT * FROM users WHERE username = ‘” + userName + “‘ AND password = ‘” + password + “‘”;
List items = sess.CreateSQLQuery(query);
If you take a close look to this example, you will see that the legitimate SQL request is injected with some user inputs without any filtering. This make the whole app vulnerable to SQL injection even if you are using an ORM.
Now, to fix this code you will need to prepare the legitimate request before injecting in a secure manner the user inputs. Here is an example of how to fix this source code:
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);
I will stop writing about this subject at this level as I think I have covered the most populare technologies for this vulnerability. Please as I have said in the begining of my posts, this is an open article anyone that want me to explain how to prevent the sql injection in a specific technology please just contact me and I will be very happy to explain it.
I hope you enjoy reading my posts … see you an other series and posts 😉
Written by: Z. Oualid
I am a Cyber Security Expert, I have worked with many companies around the globe to secure their applications and their networks. I am certified OSCP and OSCE which are the most recognized and hard technical certifications in the industry of cybersecurity. I am also a Certifed Ethical hacker (CEH). I hope you enjoy my articles :).
How to Z. Oualid
For those who didn’t see the first part. This blog post is a part of a long series where I explain in detail how to prevent the SQL injection attack ...
Copyright © 2020 Getsecureworld.
Post comments (0)