Prevent SQL Injection Attacks
Most sites have a way to store data, the most common of which is a database. When using SQL Databases to store site data, a site owner has to beware of SQL Injection attacks which can steal or alter data.
SQL Injection and How to Prevent It
Applications which return different results based on user input (such as logging a user in based on username/password) may not properly filter all user input. Thus, when a user enters some data, they may modify the data in an attempt to change the underlying SQL query against the database. If they succeed, then the attacker may be able to log in as an administrator, or steal other users data.
Imagine that you have user data stored in a database tables (users), and it has the following structure
CREATE TABLE users(
Looking at the code the site uses to log in, it dynamically builds a SQL query in PHP, after a user has given username and password in a form (with user and pass variables):
$results = mysql_query( "SELECT use_id FROM users WHERE username='".$_POST['user']."' AND password='".$_POST['pass']);
The main problem above is that data from the user is not sanitized; the POST variable is being used to pull data directly from the login form, and placing it into the query. At this point, an attacker could try putting in a special string for username:
Now the query always returns the first user (admin potentially) and the attacker is logged in as an administrator!
Other attacks are possible using similar logic. Instead of logging in as an administrator, an attacker could use other special strings to request data from the database they should not have access to, potentially gathering all the data in the database which the application database user has access to.
How Does this Impact my Security?
A successful attack would allow an attacker to steal most or all of the database, including all sensitive data, and log in with administrative privileges in the application.
SQL Injection is one of the most high risk vulnerabilities an application can have.
How to Prevent SQL Injection Attacks
SQL Injection can be prevented in a similar way to XPath injection. The best way is to carefully sanitize user input. Any data received from a user should be considered unsafe. Removing all single and double quotes should remove most types of this kind of attack.
Beware that removing quotes can have side effects, such as when usernames might contain valid quotes. Imagine common names such as O'Malley, which contains a legitimate quote and may be input on a name request form. In these cases, the input should be escaped, often by adding a \ in front of quotes. Check with your specific library and database software for the proper syntax.
Most programming libraries contain functions to help escape user input for the sake of queries. Check your specific documentation for the correct way to automatically escape data for querying SQL Databases. In PHP and MySQL, the function mysql_real_escape_string to escape dangerous query data. Using this or similar functions on all user input will help to sanitize code.
Secondly, write all SQL queries using parametrized queries, where the query is pre-defined, and strongly typed data is passed in after the fact. In PHP for example, you could re-define the query seen previously with this parametrized query to prevent SQL injection attacks:
$query= "SELECT user_id FROM users WHERE username=? and password=?";
mysqli_stmt_bind_param($preparedStatement, 'ss', $field1, $field2);
$field1 = $_POST['user'];
$field2 = $_POST['pass'];
Prevent SQL Injection Attacks on You Web Application