LII:Web Application Security Guide/SQL injection

SQL injection

An SQL injection vulnerability occurs if user input included in database queries is not escaped correctly. This type of vulnerability allows attackers to change database queries, which can allow them to obtain or modify database contents.

To prevent this type of attack

Use prepared statements to access the database – or –.
Use stored procedures, accessed using appropriate language/library methods or prepared statements.
Always ensure the DB login used by the application has only the rights that are needed.

Rationale

Escaping input manually is error-prone and can be forgotten. With prepared statements, the correct escaping is automatically applied. This also avoids issues with different input interpretation (charset, null byte handling etc.) which can lead to hard-to-find vulnerabilities. Using a database login with limited access rights limits the impact of successful attacks.

Exploitation

SQL injection can compromise any information in the database and even lead to full system compromise. It can be used to add PHP, HTML, and JavaScript code to web pages and create files. Arbitrary content added to the website can be used for malicious attacks against users and to gain shell access to the server.

Example

If the input for the title of the page on this website were vulnerable to SQL injection then the URL that would be used for the attack is https://en.wikibooks.org/w/index.php?title=. A simple test to reveal if the input is vulnerable would be to add https://en.wikibooks.org/w/index.php?title=' because this SQL syntax would break the query and show an SQL error on the page. The next query could be to select usernames and hashed passwords with something like https://en.wikibooks.org/w/index.php?title=1%20UNION%20ALL%20SELECT%20user_pass%20FROM%20wiki_user;--. The ;-- on the end ends the query and makes the remaining query a comment. Files containing password salts could be dumped to allow an attacker to begin cracking passwords and gain access to administrator accounts using the select load_file() query. A query like this one could be used to gain shell access to the server: https://en.wikibooks.org/w/index.php?title=UNION%20SELECT%20<? system($_REQUEST['cmd']); ?>,2,3%20INTO%20OUTFILE%20"shell.php";--