Using MySQLi and PDO to Prevent SQL Injection in PHP

SQL injection occurs when user input is concatenated directly into SQL statements without validation, as shown by the vulnerable example using $id = $_GET['id']; $sql = "SELECT name FROM users WHERE id = $id"; .

Converting the input to an integer with $id = intval($_GET['id']); removes unsafe characters.

String inputs should be sanitized using functions such as addslashes(sprintf("%s", $str)); or mysqli_real_escape_string , and length checks can prevent buffer overflow attacks.

Parameterized queries provide a stronger defense; MySQLi prepared statements can be used as:

$mysqli = new mysqli('localhost','my_user','my_password','world');
$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
$stmt->bind_param('sssd', $code, $language, $official, $percent);

PDO offers a similar approach:

$sql = 'SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour';
$sth = $dbh->prepare($sql);
$sth->execute([':calories'=>150, ':colour'=>'red']);
$red = $sth->fetchAll();

When custom queries are necessary, a manual preparation function can be written, as illustrated by the prepare() example that validates placeholders and escapes arguments.

The article concludes that security awareness, proper input validation, and using prepared statements are essential to avoid vulnerabilities, and it compares addslashes , mysql_real_escape_string , and mysql_escape_string functions.

addslashes() simply adds backslashes.

mysql_real_escape_string() respects the connection charset but requires a recent PHP version.

mysql_escape_string() does not consider the charset.