How to Properly Use Prepared Statements with PDO for MySQL INSERT Queries?

Executing MySQL INSERT Queries Using Prepared Statements with PDO

In the realm of PHP Data Objects (PDO), difficulties can arise when attempting to execute SQL queries through prepared statements. A common problem involves using the INSERT INTO statement to add records to a database.

Consider the following code:

$dbhost = "localhost";
$dbname = "pdo";
$dbusername = "root";
$dbpassword = "845625";

$link = new PDO("mysql:host=$dbhost;dbname=$dbname","$dbusername","$dbpassword");

$statement = $link->prepare("INSERT INTO testtable(name, lastname, age)
        VALUES('Bob','Desaunois','18')");

$statement->execute();

This code attempts to insert a new record into the testtable table in a MySQL database. However, the database remains empty.

To resolve this issue, you need to use parameterized queries instead of embedding values directly into the SQL statement. Parameterized queries use placeholders (?) or named parameters (:parameter) to represent values, which are then bound to actual values during execution. This approach prevents SQL injection attacks and ensures data validation.

The correct way to use prepared statements with INSERT INTO is as follows:

$dbhost = 'localhost';
$dbname = 'pdo';
$dbusername = 'root';
$dbpassword = '845625';

$link = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbusername, $dbpassword);
$link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$statement = $link->prepare('INSERT INTO testtable (name, lastname, age)
    VALUES (:fname, :sname, :age)');

$statement->execute([
    'fname' => 'Bob',
    'sname' => 'Desaunois',
    'age' => '18',
]);

By using parameterized queries, you protect your application from malicious input and ensure the integrity of your data.

The above is the detailed content of How to Properly Use Prepared Statements with PDO for MySQL INSERT Queries?. For more information, please follow other related articles on the PHP Chinese website!