PHP SQL Injection Prevention

Wikipedia: SQL injection is a technique often used to attack data driven applications. This is done by including portions of SQL statements in an entry field in an attempt to get the website to pass a newly formed rogue SQL command to the database (e.g., dump the database contents to the attacker).

If you are unlucky enough, attacker can compromise your whole system and control it without you noticing. But somehow we can still prevent attacker’s nasty tricks by simple sql injection prevention techniques.

During MySQL database transactions, some developers use the extension called mysqli or mysql. But there is a MySQL database abstraction layer that we developers should primarily use, PDO (PHP DATA OBJECTS).

Whats good about this? Simple, prepared sql statements.

# Ex:
$stmt = pdo->prepare('SELECT user FROM tableName WHERE password=:pwd');
$stmt->bindParam(':pwd', $varPass);
# End

This type of prepared sql statement is highly recommended by security experts when processing data between php and mysql database. Note this is only a basic prepared sql statement. For more information about PDO, you can visit its official PDO Manual.

Another solution to prevent sql injection is to use htmlentitiesand htmlspecialchars php functions.

# Ex:
$varFirst = htmlentities($_GET['theVar1']);
$varSecond = htmlentities($_GET['theVar2']);


$varFirst = htmlspecialchars($_GET['theVar1'], ENT_QUOTES); // ENT_QUOTES converts double and single quotes to html entity
$varSecond = htmlspecialchars($_GET['theVar2'], ENT_QUOTES);

# then do the prepared sql statements after

With this functions, the content will be converted to html entities and thus will be more secure before and after data insertion and retrieval. Another note, when using htmlspecialcharsfunction, you must use htmlspecialchars_decodeto retrieve contents from the database. For better understanding about this functions, visit php documentation.
Pretty basic huh? But that will help you from attacks and data leaks.