我在PHP中运行此代码时收到错误

Question

I keep receiving some variant of this error message:

Warning: PDO::exec(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@email.com",5,2)' at line 1 in C:\xampp\htdocs\donations\index.php on line 31

The PHP it is referring to is this:

$db->exec("INSERT INTO donations(name, email, donation_amount, item_id) VALUES(\"" . $_POST['name'] . "\"," . $_POST['email'] . "\"," . $_POST['amount'] . "," . $_POST['radioButtons'] . ");");

Am I not escaping correctly or do I have too many quotes? Any help is appreciated!

Answer 1

You're already on a right track using PDO. Now the next step is to use it properly by utilizing prepared statements.

That being said your code might look something like this:

//TODO Check, validate, sanitize your input...
$name = $_POST['name'];
$email = $_POST['email'];
$donation_amount = $_POST['amount'];
$item_id = $_POST['radioButtons'];

try {
    $db = new PDO('mysql:host=localhost;dbname=your_db_name', 'user', 'password');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    //Construct your query with placeholders
    $sql = "INSERT INTO donations (name, email, donation_amount, item_id) 
            VALUES (?, ?, ?, ?, ?)";
    //Prepare your query
    $query = $db->prepare($sql);

    //Execute it passing parameters
    $query->execute(array($name, $email, $donation_amount, $item_id));

} catch (PDOException $e) {
    echo "Exception: " . $e->getMessage(); //TODO better error handling
}
$query = null;
$db = null;

Further reading:

• PDO tag wiki
• A PDO tutorial
• Are PDO prepared statements sufficient to prevent SQL injection?