SQL Injection

MySQL Tutorials

SQL injection is a most common area which a hacker can exploit and which proves to be disastrous for your website. So as a professional developer you must avoid code which is prone to SQL injection in any way.

Example of SQL Injection

User will input: 105 or 1=1
your query will become:
SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1;
This will always return rows even if 105 is not present as a user-id in the database.

This can cause damage by manipulating data or deleting data to your website database.

To avoid SQL Injection there are many ways:-

  1. use prepared statements while interacting with your database whether it is select, insert, update or delete statement. If you want to execute any statement in the database then you must use a prepared statement. But your code execution time increase as a prepared statement is too slow in PHP.
  2.  basic rules using Strings in database
    • have to be added via a native prepared statement
      or
    • have to be enclosed in quotes
    • special characters (frankly – the very delimiting quotes) have to be escaped
    • proper client encoding has to be set
      or
    • maybe hex-encoded
  3. basic rules using Numbers in database
    • have to be added via a native prepared statement
      or
    • should be formatted to contain only numbers, a decimal delimiter, and a sign
  4. basic rules using Identifiers in database
    • have to be enclosed in backticks
    • special characters (frankly – the very delimiting backticks) have to be escaped
  5. basic rules using Operators and keywords in the database.
    • there are no special formatting rules for the keywords and operators besides the fact that they have to be legitimate SQL operators and keywords. So, they have to be whitelisted.
  6. To avoid SQL injection you can use mod security which is an open source web firewall in the database server.

 

These are some simple ways by which you can protect your database from SQL injection.