Prevent SQL injection attack in PHP

Question

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?

Does this answer your question? How can I prevent SQL injection in PHP? — Run_Script
– Run_Script, Commented Apr 17, 2020 at 11:35

Nick · Accepted Answer · 2020-04-17 11:46:52Z

3

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());
}

to

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).

edited Apr 17, 2020 at 11:46

answered Apr 17, 2020 at 11:36

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Prevent SQL injection attack in PHP

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related