What is SQL injection?

An SQL injection, which is also referred to as a “failure to preserve SQL Query Structure”, is a common and dangerous security issue. SQL injections are dangerous because they allow hackers to compromise your system through your web interface, letting them wreak havoc at will – i.e., modify databases, delete tables, and control your corporate network. An SQL injection is a programming error; it isn’t because of your managed hosting provider. If you’ve been looking for web hosting packages for php or secure jsp, you should know that your hosting provider isn’t responsible for preventing SQL injections; that is a web developer’s responsibility.

Why do SQL injections happen?

SQL injections are common but easily preventable. They happen often because there are many vulnerabilities present and when they are successfully exploited, the hacker can be rewarded handsomely (e.g., by having an entire database to tamper with on dedicated servers).

There are SQL injection risks whenever dynamic database queries with user input are created by programmers. The two ways to prevent SQL injections are to keep from writing dynamic database queries and to keep from allowing user input in those queries. These are simple solutions but they’re also impractical because of how useful and inevitable dynamic queries and user inputs are. You can still prevent SQL injections, however, with coding techniques that apply in all programming languages.

How can one prevent an SQL injection?

Although different programming languages will require different codes, the essential steps to prevent SQL injections are similar. The following examples can be used as guides:

1. Only use dynamic SQL if you have to. Typically you can replace them with parametrized queries, prepared statements, or stored procedures. For example, you can use PreparedStatement() in Jave with bind variables, or parametrized queries like OleDbCommand() or SqlCommand() in .Net, or strongly typed parametrized queries in PHP via PDO (using bindParam()).
Besides prepared statements, stored procedures also work. These are kept in the database unlike prepared statements, but you’ll need to define your SQL code with both before passing parameters.

2. Escape user input. This isn’t as effective as stored procedures or parametrized queries but if you can’t use those two, it’s still better to go with escaping user input than nothing. Your syntax will vary with your database, soc heck your DBdocs so you’ll have examples and proper syntax.

3. Assume magic quotes are off. When that gpc variable is off, some, though not all, SQL injection attacks will be prevented. They aren’t an ultimate defense, however, and sometimes they might be turned off without your knowledge. This is why you want to have code for substituting your quotes with slashes. Jon Lee suggested the following code piece:

$username=$_POST['username']; followed by
$pw=$_POST['pw']; followed by
if (!get_magic_quotes_gpc()) { followed by
$username=addslashes($username); followed by
$pw=addslashes($pw); followed by
} where pw is password.

Install patches often and on time. Even if you don’t have these vulnerabilities, when your development tools, operating system, or database server do, you’re still at risk. Install patches as soon as they become available.

4. Remove useless functionality. Database servers are complex and include more things than you need. More isn’t better when it comes to security. For example, xp_cmdshell is an extended store procedure that allows anyone (including hackers) to access the shell in MS SQL. Disable this and similar settings that are easy to misuse.

5. When making SQL injections, use automated testing tools. Even if developers keep in mind the rules above and try not to allow dynamic queries that include unsafe user inputs, it’s still important to confirm compliance via testing. Automated tests will check for SQL injections and you should use them on all the code included in your database applications.

A relatively reliable and easy tool for testing SQL injections is SQL Inject Me, a Firefox extension. Install it and you will be able to use it in the context menu when you right click it. You can also reach it from going from tools to options.

You can decide which tests you want to run and which values you would like to test. You can start your selected test or tests by pressing one of the buttons for testing. Once the tests are finished, you’ll receive a report that shows you what came up in the tests. There are several options you can select for the extension.

As you can see, there are lots of simple steps to take if you want to keep your code clear from possible SQL injection vulnerabilities.