Prevent SQL injection attack in PHP

I would like to prevent SQL attacks on this piece of PHP code (this is just a practice exercise in class). This can easily be exploited by setting @mail equal to something like '); DROP TABLE PURCHASE; --

$db = new SQLite3 ($db_name);

$sql = sprintf ("INSERT INTO PURCHASE (quantity, name, password, mail) 
                 VALUES ('%s', '%s', '%s', '%s')n",
                 $Quantity, $Name, $Password, $Mail );

echo $sql;
if (!$db->exec ($sql)) {
    throw new Exception($db->lastErrorMsg());
}

I tried to prevent this by passing parameters like this, but I get 500 Internal Server Error

$db = new SQLite3 ($db_name);

$sql = $db->prepare("INSERT INTO PURCHASE(quantity, name, password, mail)
            VALUES (:Quantity, :Name, :Password, :Mail)");

$sql->bindValue(':Quantity', $Quantity, SQLITE3_TEXT);
$sql->bindValue(':Name', $Name, SQLITE3_TEXT);
$sql->bindValue(':Password', $Password, SQLITE3_TEXT);
$sql->bindValue(':Mail', $Mail, SQLITE3_TEXT);

echo $sql;
if (!$db->exec ($sql)) {
    throw new Exception($db->lastErrorMsg());
}

How can I fix this?

Answer

SQLite3::exec is for executing a query string, not a prepared statement. You need to use SQLite3Stmt::execute instead. Change:

if (!$db->exec ($sql)) {
    throw new Exception($db->lastErrorMsg());
}

if (!$sql->execute()) {
    throw new Exception($db->lastErrorMsg());
}

Note you can’t echo $sql as it is an object, not a simple type. If you want to look at what a SQLite3Stmt object looks like, you would need to print_r($sql) or var_dump($sql).

Advertisement

Answer