What it is
SQL Command Injection is a technique where an attacker creates or alters existing SQL commands to expose hidden data, or to override valuable ones, or even to execute dangerous system level commands on the database host. This is accomplished by the application taking user input and combining it with static parameters to build an SQL query.
SQL queries are able to circumvent access controls, thereby bypassing standard authentication and authorization checks, and sometimes SQL queries even may allow access to host operating system level commands.
- Never connect to the database as a superuser or as the database owner. Use always customized users with very limited privileges.
- Use prepared statements with bound variables. They are provided by PDO, by MySQLi and by other libraries.
- Check if the given input has the expected data type. PHP has a wide range of input validating functions, from the simplest ones found in Variable Functions and in Character Type Functions (e.g. is_numeric(), ctype_digit() respectively) and onwards to the Perl compatible Regular Expressions support.
- If the application waits for numerical input, consider verifying data with ctype_digit(), or silently change its type using settype(), or use its numeric representation by sprintf().
- If the database layer doesn’t support binding variables then quote each non numeric user supplied value that is passed to the database with the database-specific string escape function (e.g. mysql_real_escape_string(), sqlite_escape_string(), etc.). Generic functions like addslashes() are useful only in a very specific environment (e.g. MySQL in a single-byte character set with disabled NO_BACKSLASH_ESCAPES) so it is better to avoid them.
- Do not print out any database specific information, especially about the schema, by fair means or foul. See also Error Reporting and Error Handling and Logging Functions.
- You may use stored procedures and previously defined cursors to abstract data access so that users do not directly access tables or views, but this solution has another impacts.
- You benefit from logging queries either within your script or by the database itself. Obviously, the logging is unable to prevent any harmful attempt, but it can be helpful to trace back which application has been circumvented.