What is SQL Injection
SQL injection is the practice of exploiting applications in order to gain access to and manipulate a database. In this article I will discuss examples and prevention in PHP/MySQL.
The Problem
Programmers often will test a script with the expected data that you are expecting an honest user to enter. The problem is not all users are honest. It occurs when $_GLOBALS are used within an MySQL query, without validating the input data first. There is an excellent example here of vulnerable code. In this case it is also causing unexpected behaviour as the single quote in the input data is closing the string and causing an error. Your best defence is to addslashes() before querying the database and to stripslashes() before outputting the data back to anywhere on the HTML page, including input form ‘default’ value.
If quotes aren’t escaped, attackers can use your input form to hack your website. This can mean a matching row will be returned, even though a matching user isn’t, allowing the hacker to log in for example. Even worse you could find your entire website deleted.
If you don’t un-escape the string before output you will end up with the output being escaped on the web-page – e.g. (don\’t)
Preventing SQL Injection Attacks
- Validate all user input. Check the data-type is correct.
- Suppress errors using an error handler and monitor errors using a text log outside of the public root.
- Check that the input is a format valid to the input expected (e.g. check username field only contains the acceptable characters)
- Escape special characters. See php.net/addslashes and php.net/stripslashes
- Keep your PHP installation up-to-date to take advantage of the latest security updates.
- Use a personal error handler to store errors in a text file or database and possibly alert admin via email on critical errors. If you hide the errors from the user, it will be much harder for the attacker to know if they guessed the field-names correctly.
Sources
Tags: Data Protection, MySQL, PHP, Security, SQL, SQL Injection
