Asked  4 Months ago    Answers:  5   Viewed   34 times

I started to know how prepared statement works when using MySQLi and PDO, for first step, I enabled MySQL query monitoring as mentioned here: How can I view live MySQL queries?. Then I created the following test:

Using mysqli:

$stmt = $mysqli->prepare("SELECT * FROM users WHERE username =?")) {
$stmt->bind_param("i", $user);
$user = "''1''";

server logs:

  130802 23:39:39   175 Connect   ****@localhost on testdb
    175 Prepare   SELECT * FROM users WHERE username =?
    175 Execute   SELECT * FROM users WHERE username =0
    175 Quit

Using PDO:

  $user = "''1''";
  $sql = 'SELECT * FROM user WHERE uid =?';
  $sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
  $sth->bindParam(1, $user, PDO::PARAM_INT);

Server Logs:

  130802 23:41:42   176 Connect   ****@localhost on testdb
    176 Query SELECT * FROM user WHERE uid ='''1'''
    176 Quit

However, both provides same result:

uid: 0
username: admin
role: admin

Note: uid = 0 is correct because intval("''1''") = 0

What is important here:

How PDO query gets same result while it sends different query to MySQL?

SELECT * FROM user WHERE uid ='''1'''

I found only one indication from PHP Manual: http://www.php.net/manual/en/pdo.prepare.php

Note:

Emulated prepared statements does not communicate with the database server so PDO::prepare() does not check the statement.

But I am not sure How MySQL deals with this query and substitute '''1''' with 0. At that case monitoring queries will not be accurate if using PDO, at same time, using PDO is better for knowing exact queries that sent to MySQL but not MySQLi.

Update: after changing parameter type frm integer to string:

MySQLi Log:

    188 Prepare   SELECT * FROM awa_user WHERE username =?
    188 Execute   SELECT * FROM awa_user WHERE username ='''1'''
    188 Quit

PDO Log:

    189 Query SELECT * FROM awa_user WHERE userame ='''1'''
    189 Quit

This means that MySQLi and PDO escaping the data before sending to MySQL when using string, while for integers, mysqli applies intval() or something like that before sending the query, as also answered by Bill which is correct.

 Answers

29

Your PDO is configured to emulate prepared queries, whereas mysqli is using true prepared queries.

The prepared query binds the string ''1'' as an integer parameter value. PHP coerces it to an integer using something like intval(). Any string with non-numeric leading characters is interpreted as 0 by PHP, so the parameter value sent after prepare is the value 0.

The fake prepared query uses string interpolation (instead of binding) to add the string ''1'' into the SQL query before MySQL parses it. But the result is similar, because SQL also treats a string with non-numeric leading characters in an integer context as the value 0.

The only difference is what ends up in the general query log when the parameter is bound before prepare versus after prepare.

You can also make PDO use real prepared queries, so it should act just like mysqli in this case:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

PS: This may demonstrate a good reason why it's customary to start id values at 1 instead of 0.

Wednesday, August 18, 2021
 
derobert
answered 4 Months ago
63

fetchAll returns an array containing all of the result set rows. So you can access to password with $data_array[0]['password'] if you used it. You may want use fetch instead.

$data_array = $stmt->fetch(PDO::FETCH_ASSOC);
Saturday, May 29, 2021
 
JakeGR
answered 7 Months ago
88

Delete your other question, ok?

The problem is you loop through $_SESSION and use the same name value each time. You need to create an array of your inputs. Here is an example:

<?php
echo '<h3>Your Order</h3>';
$current_url = base64_encode($url='http://'.$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI']);
  if(isset($_SESSION['products'])){
     echo '<ol>';
     echo '<form action="checkout_with_us.php" method="POST">';
     $total = 0;
     $cart_items = 0;

        foreach($_SESSION['products'] as $cart_itm){
           $product_code = $cart_itm['code'];
           $results = $mysqli->query("SELECT product_name,product_desc,price FROM products WHERE product_code='$product_code' LIMIT 1");
             $obj = $results->fetch_object();
                echo '<li>';
                echo 'Price: '.$currency.$obj->price;
                echo '<h4>'.$obj->product_name.'(Code: '.$product_code.')</h4>';
                echo 'Qty: '.$cart_itm['qty'];
                echo '</li>';

                   $subtotal = ($cart_itm['price'] * $cart_itm['qty']);
                     $total = ($total + $subtotal);
                     $cart_items++;
                       echo '<input type="hidden" name="product['.$product_code.'][item_name]" value="'.$obj->product_name.'">';
                       echo '<input type="hidden" name="product['.$product_code.'][item_desc]" value="'.$obj->product_desc.'">';
                       echo '<input type="hidden" name="product['.$product_code.'][item_qty]" value="'.$cart_itm["qty"].'">';
                       echo '<input type="hidden" name="product['.$product_code.'][item_code]" value="'.$product_code.'">';     
                }
                    echo '<strong>Sub Total: '.$currency.$total.'</strong>';
                    echo '<input type="hidden" name="product['.$product_code.'][price]" value="'.$total.'">';
                    echo '</ol>';   
                    }

//Here is the information of the customer
echo 'Firstname: <input type="text" name="firstname"><br />';
echo 'Lastname: <input type="text" name="lastname"><br />';
echo 'Email: <input type="text" name="email"><br />';
echo '<input type="submit" value="Send Step">';

echo '</form>';
?>

You can catch this by looping in your product array:

<?php
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];

$conn = mysqli_connect('localhost','root','','sampsix')or die('Could not connect');

foreach($_POST['product'] as $product)
{
    $order_name = $product['item_name'];
    $order_code = $product['item_code'];
    $order_qty = $product['item_qty'];
    $sub_total = $product['price'];

    $query = "INSERT INTO `sampsix`.`orders`(`firstname`,`lastname`,`email`,`OrderName`,`OrderCode`,`OrderQty`,`SubTotal`) VALUES('$firstname','$lastname','$email','$order_name','$order_code','$order_qty','$sub_total')";
    mysqli_query($conn,$query);
}



mysqli_close($conn);

header('Location: checkout.php');
?>

I don't know what the purpose is of the table orders but with my example the products will be added to this table with the same firstname, lastname, etc.

Saturday, May 29, 2021
 
Ula
answered 7 Months ago
Ula
31

All of the mysqli functions/methods can fail in which case they will return false. I.e. if prepare() fails $stmt isn't an object you can call a method on but a bool(false). You have to check the return values and add some error handling, e.g.

$stmt = $mysqli->prepare('SELECT name FROM `rooms` WHERE r_id=?');
if ( !$stmt ) {
    printf('errno: %d, error: %s', $mysqli->errno, $mysqli->error);
    die;
}

$b = $stmt->bind_param('i', $roomID);
if ( !$b ) {
    printf('errno: %d, error: %s', $stmt->errno, $stmt->error);
}

$b = $stmt->execute();
if ( !$b ) {
  and so on and on

see http://docs.php.net/mysqli-stmt.errno et al


in this case you probably bumped into the problem that you can't create an other statement while there are still results/result sets pending for the previous statement.
see http://docs.php.net/mysqli-stmt.close:

Closes a prepared statement. mysqli_stmt_close() also deallocates the statement handle. If the current statement has pending or unread results, this function cancels them so that the next query can be executed.
Wednesday, September 1, 2021
 
Ryan Stewart
answered 3 Months ago
84

Found a faster solution which saves about 2-3 seconds when updating 500 records and inserting 500 records.

function newSQL() {
    global $server, $username, $password, $database;
    $con = new mysqli($server, $username, $password, $database);
    return $con;
}

$mysqli = newSQL();
$mysqli->multi_query($multiUpdates);
$mysqli->close();

$mysqli = newSQL();
$mysqli->query($sqlInserts);
$mysqli->close();

Not sure how practical it is but works well for speed.

Friday, November 19, 2021
 
treeface
answered 2 Weeks ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share