How to prevent SQL injection?

When creating a site its security should be in considered high in your priority list. A lot of people nowadays use free tools, weak passwords, and unsafe code. All this could easily result in your site being compromised, and all of your information being exposed or lost. In our series of articles, which we started with how to create a strong password, we will try to give you some tips on how to create and manage your site so that its less vulnerable to attacks.

Prevent SQL injection

The focus of this article is means to prevent SQL injection attacks. Many of you familiar with SQL should be already are aware of this threat, but for those who are not, SQL Injection is a way of inserting a malicious code into a web form as a part of a SQL query, which convinces the application to run the newly inserted SQL code instead of the original SQL code. When the SQL Injection is successful  the database and of its data is at risk. The SQL injection allows the intruder to read, update, alter, and even delete the data in the database. And yes, this includes sensible information, such as passwords, and credit cards, which is usually the main motivation for this type of attacks.

The ways to protect your site from such intrusions are not that complicated. With over 20% of the web vulnerabilities being related to SQL injections, it is essential  that developers involved with SQL need to make sure that at least the basic measures are taken to prevent this sorts of vulnerabilities.

  How to protect your database:

  1. Never trust the user – wherever there is a user input, you have to make sure that you always protected your database. If for example the user input expected is a number (e.g. a phone number field), make sure that you have secured your database by defining in your application web form, that only numbers are accepted as valid value for that field. To see why user input validation is essential in order to prevent SQL injection attacks, let’s try with a real world scenario – suppose that an attacker decides to test your application’s login form for vulnerabilities. In your application’s source code the SQL query, that checks if a given user exists in your database, might look like this:

SELECT * FROM users WHERE email = ‘<user_input_from_the_login_form>’;   If the attacker fills up this – 1′ OR ‘1’ = ‘1 – in the username field and you haven’t done any input validation, the final query that will be executed will look like this:   SELECT * FROM users WHERE email = ‘1’ or ‘1’ = ‘1’; The attacker has simply provided an additional condition to the query that is always true (1 = 1) and the result is that he will probably get the whole content of your users database which will most likely include e-mails, usernames, password hashes and so on. You can take a few simple measures to prevent this kind of attack:

  • Escaping quotes in text input – like you saw in the above example, quotes have special meaning in SQL queries, so it’s essential to escape all quotes in the user input. Most programming languages provide build-in functions for escaping text input prior of inserting it into the database (e.g. PHP’s mysql_real_escape_string() function). Escaped quotes will have no special meaning in the above example and the query will execute properly.
  • When validating usernames and passwords, limit the number of returned rows – when you are checking if user exists in the database you only need one row from the table, not all rows in the table. Using MySQL LIMIT clause or Microsoft SQL TOP clause will ensure that even if an attacker successfully executes SQL injection like in the previous example, he will only get the details for one user:

SELECT * FROM users WHERE email = ‘<user input>’ LIMIT 1; SELECT TOP 1 * FROM users WHERE email = ‘<user input>’;

  • When checking if data exists or not, select only one field from the table – in any circumstances do NOT use * (all fields). For example, if you’re only checking if the given user e-mail address already exists, select only the user ID. In case the SQL injection infiltrates your database, only the user IDs will be exposed, but not all user information:

SELECT userid FROM users WHERE email = ‘<user input>’ LIMIT 1;

  • Provide minimum access to the database – Following the main rule which was “Never trust the user”, another measure to protect your account is to provide users with access to the database with the minimum level of access needed. If a user needs only ‘select’ do not give permissions to ‘delete’ or ‘drop’ tables. Also if you use different applications, do not forget to separate the access their databases, meaning that if a user needs access to a certain application, this means exactly that and not access to other applications as well.
  1. Implementing mod_security – this is another protection which increases the security of your databases. It is a application based firewall that provides protection from a range of attacks.
  2. Using stored procedures – this is mainly useful when a database is intended to be shared. If you have a lot of users with access to the database, you should make sure that the data will be validated first. And since we have agreed above, that you never trust the user, meaning in this case that you do not know what type of validation they have created and how secure it is, you should make sure that you create a validation by yourself. What you do is you create custom stored procedures (functions, stored directly into the database) for all operations that the users will perform like select, insert etc. The stored procedures will validate the data before performing the requested operations. Then you expose only these functions to the users and they will use them as if they were using the real SQL statements. This actually creates an abstract layer, which in this case could serve as another protection shield of your database, since the users do not interact directly with it.
  3. Using prepared statements / parameterized queries – when you are using prepared statements instead of building the SQL query dynamically, you first make a template of the whole query, substituting all parts which will contain user input, with a parameter (for example a question mark). Then, when the prepared statement is executed, all parameters are replaced with the actual user input, the data is validated and only then the real query gets executed. Most popular programming languages like Java, C#, Perl, PHP have either build-in support for prepared statements or offer external libraries that let you use this approach in your application.
  4. Salted hashes – Let’s say that unfortunately, the attacker has found a weak point in your security and have breached it. Something that you can do to ensure some security to your users is using the so called ‘salted hashes’ for their passwords. What it does is adding some random characters (the ‘salt’) to the password and then this string is converted in the so called hash. Taking into account that the majority of people use the same password for different accounts, at different sites, in case your data is exposed, at least the attacker can use the passwords only for your site and all other accounts of the users will not be compromised.

If you follow all the steps mentioned above, you will ensure that your databases are much more secured against SQL injections. And considering the amount of brute force attacks based on this method, your application has just gained 20% extra security. Which is by far not a bad percentage, right?

Hope it was helpful, please leave your comments in the comment section below or send me an email at [email protected]

Happy Holidays,


Leave a Reply

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.