10 step guide to prevent SQL injection

Hello Friends, it new year and now lets dedicate this year to Web security and ethics.

Note my point:

” Security doesn’t suffers because of Hackers, It suffers because of unaware developers and inappropriate programming techniques”.

So friends, let have a resolution this year that we will learn everything and utilize our knowledge only for Good causes and HackingLoops will always be there to help you in fulfilling your resolution.

And accept one bitter truth :

“You can never stop hackers to hack something, you can just make his task harder by putting some extra security”

SQL injection is one of the most common and dangerous security threat in the software and web industry. SQL injection is nothing, just a failure to prevent the applications (web or software) database structure consistent. SQL injections are dangerous because they are a open windows to hackers to enter in your system through your Web interface and perform whatever they like i.e. delete tables, modify databases, exposing your users informations, or even get hold of your corporate network.

Complete Guide to Stop or Prevent SQL injection

Some people say SQL injection is because of web hosting providers weak security system but my dear friends, SQL injection is a programming based issue, and believe me it has nothing to do with Web hosting providers. It’s completely a programmatic issue i.e programmer has forgot to handle the strings properly in its application or sometimes doesn’t handled the dynamic queries and its variables correctly. ok..ok.. Let me explain this in detail how SQL injection or any other web or application injection attack works?

My first question is that why SQL injection attacks occurs so frequently?

The answer is quite simple, SQL is most popular language for database management and all know, popularity drives risks. More people know the things, more vulnerabilities can be discovered. But this is partially true in case of SQL injection. I can understand sometimes loophole or bug lies with the programming language but most of times its because of lack of sufficient knowledge. When i was a college student, i attended a SQL coaching classes, i appreciate what knowledge they have provided me but its ironical, they never taught me secured programming practices and not a single lecture on how to harden the SQL server and how to fix its loopholes. And i can guarantee its the same case for most of us and programmers or web developers that suffers most are self learners. Always consider the fact 

“Little knowledge is a dangerous thing”.

SQL injection risks arise every time when a programmer creates a dynamic database query, which contains or accepts user input. So SQL injection can be prevented by following two things:

1. Avoiding use of dynamic queries.

2. Not allowing user inputs in your queries

Oops! both solution sounds little bit weird isn’t it… off  course these solutions sounds ridiculous, as most of queries are dynamic in case of complex websites, and second if you don’t allow user input into your queries, efficiency will be hugely affected. Accessing user data will going to take ample amount of time and everybody knows, users don’t like to wait… 

So friends lets see the solutions in more technical way, that which 10 steps can protect your website from SQL injection:

 1. Use dynamic SQL if and only if there is no other alternative

 2. Escape user input always

 3. Always assume magic quotes is off

 4. Install security updates and patches regularly

 5. Remove all the dead SQL’s or other codes that you don’t use

 6. Never display the system defined error message for SQL errors 

 7. Store database credentials in a separate file

 8. Use the principle of least privilege

 9. Disable shells

 10. Use SQL injection Hack tools to check vulnerabilities

Lets discuss these techniques one by one in detail to make our website fire proof against all SQL injection hacking attempts:

1. Use Dynamic SQL queries if and only if there is no other alternative

Dynamic SQL can almost always be replaced with prepared statements, parameterized queries, or stored procedures. For instance, instead of dynamic SQL, in Java you can use PreparedStatement() with bind variables, in .NET you can use parameterized queries, such as SqlCommand() or OleDbCommand() with bind variables, and in PHP you can use PDO with strongly typed parameterized queries (using bindParam()).

In addition to prepared statements, you can use stored procedures. Unlike prepared statements, stored procedures are kept in the database but both require first to define the SQL code, and then to pass parameters.

Parameterized Queries:

SQL Server, like many other database systems, supports a concept called parametrized queries. This is where the SQL Query uses a parameter instead of injecting the values directly into the command. 

Consider the following Query:

string cmdText=string.Format(“SELECT * FROM Customers “+
    “WHERE Country='{0}'”, countryName);
SqlCommand cmd = new SqlCommand(cmdText, conn);

the same query can easily rewritten using parameters:

 string commandText = “SELECT * FROM Customers “+
    “WHERE Country=@CountryName”;
SqlCommand cmd = new SqlCommand(commandText, conn);
cmd.Parameters.Add(“@CountryName”,countryName);

The value is replaced by a placeholder, the parameter, and then the parameter’s value is added to the Parameters collection on the command.

Using Stored Procedures

Stored Procedures add an extra layer of security in to the design of a software or web application. In this case, as long as the interface on the stored procedure stays the same, the table structure can change with no noticeable consequence to the application that is using the database. This layer of abstraction also helps put up an extra barrier to potential attackers. If access to the data in SQL Server is only ever permitted via stored procedures, then permission does not need to be explicitly set on any of the tables. Therefore, none of the tables should ever need to be exposed directly to outside applications. For an outside application to read or modify the database, it must go through stored procedures. Even though some stored procedures, if used incorrectly, could potentially damage the database, but anything that can reduce the risk is beneficial.

Stored procedures can be written to validate any input that is sent to them to ensure the integrity of the data beyond the simple constraints otherwise available on the tables. Parameters can be checked for valid ranges. Information can be cross checked with data in other tables.

2. Escape User Input

The second biggest evil for SQL injections is user input. While you can’t always avoid user input completely, the next best thing is to escape it. Escaping user input doesn’t do as good job as limiting dynamic queries but still it can stop many SQL injection attacks. For instance, if you are using PHP, for GET and POST, use htmlspecialchars() to escape XSS characters and addslashes(), in case you using database. Alternatively, you can escape user input from inside your database but since the exact code varies from one database to the next, you should check with the docs of your database for the exact syntax to use.

3. Always assume magic quotes is turn off

When the magic_quotes_gpc variable is off, this can prevent some (but not all) SQL injection attacks. Unfortunately Magic quotes are not an ultimate defense against SQL injection and but their is much worse – sometimes they are turned off by default and you more worse is that you are not aware about it. This is why it is necessary to have code for the substitution of quotes with slashes. 

Here is the simplest way to turn off magic quotes:

$username = $_POST[‘username’];
$password = $_POST[‘password’];
if (!get_magic_quotes_gpc()) {
   $username = addslashes($username);
   $password = addslashes($password);

4. Install security updates and patches regularly

Even if your code doesn’t have SQL vulnerabilities, when the database server, the operating system, or the development tools you use can have SQL vulnerabilities, and this is also risky. And everything that can become a risk should be taken care of. This is why you should always patch your system, especially your SQL server.

5. Remove all the dead SQL or other codes that you don’t use

Database servers are complex beasts and they have much more functionality than you need. As far as security is concerned, more is never better. For instance, the xp_cmdshell extended stored procedure in MS SQL gives access to the shell and this is just what a hacker dreams of. This is why you should disable this procedure and any other functionality, which can easily be misused. Just remove or at least disable any functionality you can do without.

6. Never display the system defined error message for SQL errors

Error messages are useful to an attacker because they give additional information about the database and SQL queries. And all SQL attacks are usually based on type of error issued by SQL means type of error decides the hackers approach for hacking the website or application. A better solution that does not compromise security would be to display a generic error message that simply states an error has occurred with a unique ID. The unique ID means nothing to the user, but it will be logged along with the actual error diagnostics on the server which the technical support team has access to. This will protect your system even if SQL throws errors, hackers will never came to know that what error occurred. So play safe to enjoy safe.

7. Store database credentials in a separate file

In order to minimize the damage in case of an SQL injection attack, always store database credentials in a separate encrypted file. Now even if a hacker manages to break in, he or she won’t benefit much as he cannot do much in your database.

8. Use the principle of least privilege

The principle of least privilege is highly beneficial and it applies to SQL injections as well. Always think or check twice about what privileges you are providing to user or object. Suppose you wan to provide moderator access to some user, so only provide him the access of those tables which he/she need, rather than proving him the access of whole database. If you have to provide access to a system, its better to create partitioned table spaces inside database and provide access only to specific table space. This technique will drastically reduce the attack surface.

9. Disable Shells

Many databases offer shell access to the database which essentially is what an attacker or hacker needs. Because of this you need to close this open loophole. Every service provider has different method to disable the execution of shells on their database. So consult your Database documentation about how to disable shell access for your particular database or table space or particular table.

10. Use SQL injection Hack tools to check vulnerabilities 

Last but not the least, think like hacker. How a hacker can hack my database through SQL injection, what tools and techniques he can use to find the loopholes. You should always have a dry run of SQL injection hack tools like SQLi, Haviz, SQL injectme etc.  More if you can afford retina vulnerability scanner then its too good. As it consists of all latest exposed vulnerabilities.

That’s all for today my friends, one more time Happy new year.

Enjoy security, love security and live safely.

If you have any issues or concerns ask me in form of comments.