Cyber Security - Preventing SQL Injection

Posted on - Last Modified on

What is Cyber Security?

Cyber Security is a collection of processes, guides, blueprints and methods that can help protect your data, its confidentiality, integrity, and availability. Cyber Security protects assets like machines, servers and most importantly, humans. According to a study made by HP Enterprise Security, in 2014 the cost of cybercrime was $12.7 million.

What is SQL Injection?

SQL injection is one of the most widely used code injection type attacks, where the executed SQL leads to a data breach or data loss, as well as reputation and intellectual property loss too. SQL injection always exploits a security vulnerability in the application, like using non-validated user input for building up a database query string.

In 2013, the OWASP rated SQL injection as the number one cyber attack. Depending on the application and the attacker's skill, SQL injection data can be read, deleted, updated, and database server privileges can be changed.

Common Scenarios

The most common scenario of SQL injection appears when developers are using string concatenation for building up the query strings, which are executed on the database server. Let's suppose that we want to validate a user when he tries to log in. The SQL that we would like to execute is (johndoe and mysupersecretpassword comes from user input):

SELECT * FROM dbo.Users u 
WHERE u.user_name = 'johndoe'
AND u.password = 'mysupersecretpassword'

Constructing and executing the SQL from .NET code:

string userName = … ; //coming from input
string password = … ; //coming from input

string loginQuery = “SELECT * FROM dbo.Users u WHERE u.user_name = '” + userName + “' AND u.password = '” + password + “' ”;

var sda = new SqlDataAdapter(loginQuery, connection);
var userDetails = new DataTable();
userDetails.Fill(dt); //we display the userDetails in a grid

When we construct the loginQuery we are directly using the input coming from the user interface. In case a malicious user enters the following text in the User Name input on the user interface: johndoe ' OR 1==1 --, our query string will be:

SELECT * FROM dbo.Users u 
WHERE u.user_name = 'johndoe' OR 1==1 --'
AND u.password = 'mysupersecretpassword'

Basically, we commented out the where clause for the password validation and made the query to return all the items from the Users table. This was achieved with the OR 1==1 part, always evaluates to TRUE.

This attack can be improved in a way. Let's say if the credentials that our application uses to connect to the database is set with an Admin role on the database, then all the tables can be listed (in MySQL) with the following input:

SELECT * FROM dbo.Users u 
WHERE u.user_name = 'johndoe' OR 1==1; 
select * from information_schema.tables; -–' AND u.password = 'mysupersecretpassword'

From the above query, the attacker can find out what tables are stored in the database. When this happens, nothing will be able stop him from accessing and stealing data.

How to prevent SQL Injection?

Here are the best practices that can help prevent SQL injection attacks:

  1. Setting database permissions correctly – The credentials we use to connect to the database server should have minimal privileges. Avoid using users with Schema Admin or Admin roles on the database server.

  2. Input validation – User input always has to be validated on the UI and the backend side as well.

  3. Using prepared statements – In each programming language there are constructs which support passing parameters to query strings in a safe and secure way. These can be index- or key/value-based. These ensure protection against SQL injection attacks.

    Example in .NET:

string loginQuery = “SELECT * FROM dbo.Users u WHERE u.user_name=@user_name AND u.password=@password”;

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(loginQuery, connection);

    command.Parameters.Add("@user_name", SqlDbType.NVarChar);
    command.Parameters.Add("@password", SqlDbType.NVarChar);

    command.Parameters["@user_name"].Value = userName;
    command.Parameters["@password"].Value = password;
}

      4. Use stored procedures for loading data. Given that, stored procedures have a fixed number of parameters. When the attacker is trying to do an SQL injection, the query execution will likely fail with some exception and it will not breach data. While it's not an elegant solution, but it's still better than handling over our data.

How to store passwords?

The example above – besides having an SQL injection vulnerability – has a bad design pattern because it stores the passwords along with the user data. Moreover, it also stores the password as plain text.

Passwords should never be stored as plain text in the database.

One of the best practices is to make some modification to the password, like replacing vowels with numbers and appending some application specific text. After these modifications are done, applying a hash function will do the trick. Only the result of the hash function should be stored to the database.

For example, while registering, the user chooses a password like ilovefreelancing. Let's replace the vowels with numbers, 1l0v3fr33l7nc1ng; and add an application specific string: myApp.

The modified user password is 1l0v3fr33l7nc1ngmyApp. When we execute an md5 hash on it, the result is: 5991d8b47aaa4e8b5b481432e948a652. This is what we should store in the database. When the user logs in, we only have to apply the same logic to the user input and compare the current result with the value in the database.

In this case, even if the user's information is accessed after a cyber attack, finding the real passwords assigned to the users can be made more difficult because there were 2 changes made to the initial password before creating the hash. The attacker could be more efficient in finding out the passwords if he knows the specific string used, plus what changes were made to the password (such as replacing the vowels with numbers).

As everything is on the Internet and we are migrating all of our stuff to the “cloud”, the security of our personal data should always take top priority. By being attentive and following a few best practices, we can protect our client's data and our reputation as software engineers.

Posted 12 March, 2015

Greg Bogdan

Software Engineer, Blogger, Tech Enthusiast

I am a Software Engineer with over 7 years of experience in different domains(ERP, Financial Products and Alerting Systems). My main expertise is .NET, Java, Python and JavaScript. I like technical writing and have good experience in creating tutorials and how to technical articles. I am passionate about technology and I love what I do and I always intend to 100% fulfill the project which I am ...

Next Article

Node.js with Mongoose