PDO prepared statement

[Sub_Seven]

Hello everyone,

I am trying to use PDO for my new projects and I can't seem to understand or at least find a good example on prepared statements, I have an update query that works fine but I know I'm not using it right for the data sanitation feature that PDO offers, this is my code:

$data = $conn->query("SELECT * FROM adminUsers INNER JOIN permissions ON adminUsers.adminUserID=permissions.adminUserID");
while ($row = $data->fetch(PDO::FETCH_ASSOC)){
    $sql = "UPDATE adminUsers, permissions SET 
        adminUsers.name = '".$_POST['name']."', 
        adminUsers.lastName = '".$_POST['lastName']."', 
        adminUsers.email = '".$_POST['email']."', 
        permissions.superAdmin = '".$_POST['superAdmin']."' , 
        permissions.editAdminUsers = '".$_POST['editAdminUsers']."' , 
        permissions.deleteAdminUsers = '".$_POST['deleteAdminUsers']."' , 
        permissions.editUsers = '".$_POST['editUsers']."' , 
        permissions.deleteUsers = '".$_POST['deleteUsers']."' 
    WHERE adminUsers.adminUserID = '".$_POST['id']."' && permissions.adminUserID = '".$_POST['id']."'";
    $sql = $conn->prepare($sql);
    $sql->execute(array(':name'=>$row['name'],
                        ':lastName'=>$row['lastName'],
                        ':email'=>$row['email'],
                        ':superAdmin'=>$row['superAdmin'],
                        ':editAdminUsers'=>$row['editAdminUsers'],
                        ':deleteAdminUsers'=>$row['deleteAdminUsers'],
                        ':editUsers'=>$row['editUsers'],
                        ':deleteUsers'=>$row['deleteUsers']));

} 

Could someone please tell me what is the right way to use PDO in this case?

Thanks a lot for any help provided.

[NogDog]

You want to put your ":text" place-holders in the query string itself where you currently have $_POST values. Then when you "bind" values to those parameters, PDO will take care of the sanitation for you. Much simplified:

$sql = "
    UPDATE table_name
    SET some_field = :input_value
    WHERE another_field = :id_value
";
$stmt = $pdo->prepare($sql);
$stmt->execute(
    array(
        ':input_value' => $_POST['input_value'],
        ':id_value'    => $_POST['id_value']
    )
); 

[Sub_Seven]

Thanks for the reply Nogdog,

I made the changes and I passed a value like "O'reilly" to the DB and it didn't really convert it to anything, it went into the table as "O'reilly", am I missing something here?

Thanks.

[NogDog]

Thanks for the reply Nogdog,

I made the changes and I passed a value like "O'reilly" to the DB and it didn't really convert it to anything, it went into the table as "O'reilly", am I missing something here?

Thanks.

That means it worked.

You don't want to convert it to anything in the actual data. Whatever escaping happens is only for the actual query string so that the MySQL parser knows to treat it as a literal character, much like you might us a "\" to escape a literal quote in a PHP echo:

echo "\"This is a test,\" he said"; 

Which will output:

"This is a test," he said

...without those backslashes.

[Sub_Seven]

Oh cool!

May I ask one more question now that we're at it?

As you can see in my original code I am updating two tables from within the same query, no matter what I do, this $count = $sql->rowCount(); will always return 0 (zero), is this the best method to get the number of affected rows here?

Thanks again for the help

[NogDog]

As far as I know, that should work okay for an insert or update query in MySQL (but it may not work for select queries). If it's not working for you, we may need to see exactly where and how are are calling it.

PS: You may want to check if it's actually returning 0 or false:

$count = $sql->rowCount();
if($count === false) {   // note use of "===" operator, not "==", to differentiate 0 and FALSE
   //some debug code here
} 

[Sub_Seven]

It returns 0 all the time, it's not false (I just checked to be sure).

Let me give you all the code to see if you can spot where the problem is:

if($_POST['submit'] == 'Edit User'){
    $data = $conn->query("SELECT * FROM adminUsers INNER JOIN permissions ON adminUsers.adminUserID=permissions.adminUserID");
    while ($row = $data->fetch(PDO::FETCH_ASSOC)){
        $sql = "UPDATE adminUsers, permissions SET 
            adminUsers.name = :name, 
            adminUsers.lastName = :lastName, 
            adminUsers.email = :email, 
            permissions.superAdmin = :superAdmin, 
            permissions.editAdminUsers = :editAdminUsers, 
            permissions.deleteAdminUsers = :deleteAdminUsers, 
            permissions.editUsers = :editUsers, 
            permissions.deleteUsers = :deleteUsers 
        WHERE adminUsers.adminUserID = :id && permissions.adminUserID = :id";
        $sql = $conn->prepare($sql);
        $sql->execute(array(':name'=>$_POST['name'],
                            ':lastName'=>$_POST['lastName'],
                            ':email'=>$_POST['email'],
                            ':superAdmin'=>$_POST['superAdmin'],
                            ':editAdminUsers'=>$_POST['editAdminUsers'],
                            ':deleteAdminUsers'=>$_POST['deleteAdminUsers'],
                            ':editUsers'=>$_POST['editUsers'],
                            ':deleteUsers'=>$_POST['deleteUsers'],
                            ':id'=>$_POST['id']));

        $count = $sql->rowCount();

        if($count == 1){
            $userEdit = 'yes';
        }elseif($count == 0){
            $noEdit = 'yes';
        }
    }
} 

I display different HTML blocks based on whether those variables are set with isset().

Thanks

[NogDog]

Check out this comment on the PHP site and see if adding that last parameter to your PDO object creation resolves this: http://www.php.net/manual/en/pdostat...unt.php#104930