Storing customers' orders

Question

I'm developing a shop system and using this method to store the customers with their respective orders:

try
{
    $connection = new PDO("mysql:host={$HOST};dbname={$DB_NAME}", $USERNAME, $PASS);
}

$connection->beginTransaction();
$sql = "INSERT INTO orders (customer_id, customer_name, order_value, order_date)
        VALUES (?, ?, ?, ?)";

$query = $connection->prepare($sql);
$query->execute(array
(
    $user_id,
    $user['user_name'],
    $order_value,
    $date
));

$id_of_respective_order = $connection->lastInsertId();

$sql = "INSERT INTO purchased_products (order_id, product_name, product_price, quantity)
        VALUES (?, ?, ?, ?)";

$query = $connection->prepare($sql);

foreach($_SESSION['cart'] as $product)
{
    $query->execute(array
    (
        $id_of_respective_order,
        $product['product_name'],
        $product['product_price'],
        $product['quantity']
    ));
}

$connection->commit();

By using this, later I can show all the products that belongs to their respective customers by using the Foreign Key order_id.

My question is simple: is this secure? If many people buy at the same time can I have problems? Is there a possibility of a product being directed to the wrong customer? Can I trust in this method?

Answer 1

My question is simple: is this secure?

It's safe from SQL injection attacks.

If many people buy at the same time can I have problems?

Since you insert the order and the cart contents in a transaction, the table will be locked until you have inserted everything, preventing other users from placing their orders. So the transaction can become a bottleneck. However, this is the right way of doing it, if it's important for you to avoid partial orders.

Is there a possibility of a product being directed to the wrong customer?

One thing is not clear, what happens if the first query fails. You didn't check for errors, though I would guess that in that case you won't get a meaningful insert ID (would have to check the docs). In any case, you should check for errors after all insert operations and handle gracefully.

Other than that, the transaction guarantees that order IDs won't get mixed up. Even without the transaction, probably it wouldn't happen, but I'd have to dig into the docs for that.

Can I trust in this method?

Yes, keeping in mind the potential bottleneck and other considerations above.

Answer 2

There are one thing which makes me wonder. You are using a transaction, which is good, but there is no rollback() if the query fails.

I would pass an array of connection parameters to the PDO object to make sure exceptions are thrown and prepared statements are forced (if the driver doesn't support prepared statements emulated prepares will be used anyways).

$options = [
    PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
];

$pdo = new PDO('mysql:host=127.0.01;dbname=example', $username, $password, $options);

I would then enclose every query within a try-catch block and rollback if an error occurs.

$pdo->beginTransaction();

try {

    // Insert query into orders table.

}catch(PDOException $exception) {

   $pdo->rollback(); // Undo any changes.

   // Handle the exception gracefully or throw it again.

}

// Same procedure for purchased_products table

$pdo->commit();

This should help you avoid inconsistent data if one of your queries fails.

Happy coding!