How can I use prepared statements combined with Transactions with PHP?

Question

My goal is to use a transaction and a prepared statement simultaneously, to achieve both integrity of data, and prevention of SQL injection.

I have this:

   try {
        $cnx = new PDO($dsn,$dbuser,$dbpass);   
        $cnx->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $cnx->beginTransaction();
        $cnx->query("SELECT * FROM users WHERE username=$escaped_input");
        $cnx->query("SELECT * FROM othertable WHERE some_column=$escaped_input_2");

        $cnx->commit();
    }

    catch (Exception $e){
           $cxn->rollback();
           echo "an error has occured";

    }

I would like to incorporate the query as one would with a prepared statement:

$stmt=$cxn->prepare("SELECT * FROM users WHERE username=?");
$stmt->execute(array($user_input));

$stmt_2=$cxn->prepare("SELECT * FROM othertable WHERE some_column=?");
$stmt_2->execute(array($user_input_2));

How can I achieve that?

Edit

I get this error:

PHP Parse error: syntax error, unexpected T_CATCH

Here is my updated code:

try 
{
    $cnx = new PDO($dsn,$dbuser,$dbpass);   
    $cnx->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $cnx->beginTransaction();
    $stmt=$cnx->prepare("SELECT * FROM users WHERE username=?");
    $stmt->execute(array($username));

    $cnx->commit();

    while ($row=$stmt->fetch(PDO::FETCH_OBJ)){
    echo $stmt->userid;

}

catch(Exception $e) { 
    if (isset($cnx)) 
        $cnx->rollback();
       echo "Error:  " . $e; 
    }

No because I don't know what to do with `commit()` and `execute()` because they are supposed to do roughly the same thing — alexx0186, May 17 '12 at 20:07
No, "commit" and "execute" *don't* "do roughly the same thing" ;) — paulsm4, May 17 '12 at 20:19
I was expecting this reply, i was asking for it.. :) What does commit do? I thought it submitted/executed all queries altogether — alexx0186, May 17 '12 at 20:21
Please read my original reply below :) It should also explain the difference between "autocommit" (which might be the source of your confusion) and "begin tran/commit". Also, you should easily be able to put both selects into the same statement (without any transaction at all). — paulsm4, May 17 '12 at 20:23
Hi, thanks for your response. Actually I use SELECT statement just for the sake of the example, but I want to be able to make 2 or 3 UPDATE statements within a transaction. Regards — alexx0186, May 17 '12 at 20:30
you're missing a close brace on the WHILE statement at the end — Andrew Willis, May 17 '12 at 20:30

paulsm4 · Answer 1 · 2012-05-17T21:01:17.113

Just call "execute" after you call "beginTransaction".

Where you call "prepare" doesn't really matter.

Here's a complete example:

http://php.net/manual/en/pdo.begintransaction.php

EXAMPLE:

 try {
    $cnx = new PDO($dsn,$dbuser,$dbpass);   
    $cnx->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $cnx->beginTransaction();

    $stmt=$cxn->prepare("SELECT * FROM users WHERE username=?");
    $stmt->execute(array($user_input));

    $stmt_2=$cxn->prepare("SELECT * FROM othertable WHERE some_column=?");
    $stmt_2->execute(array($user_input_2));

    $cnx->commit();
  }
  catch (Exception $e){
    $cxn->rollback();
    echo "an error has occurred";
  }

PS: 1) I'm assuming, of course, that $user_input and $user_input_2 are available immediately. You don't want your transaction hanging open unnecessarily long ;)

2) Based on your comment reply above, I think you might be confusing "execute" and "begin tran/commit". Please look at my link.

3) Do you even need a transaction? You're just doing two "select's".

4) Finally, why not do one "join" (or union, if compatible) instead of two "select's"?

I suggested a join in my answer, I think that's what he meant by "Incorporate as one" — Andrew Willis, May 17 '12 at 20:26

Jeroen · Accepted Answer · 2012-05-17T20:42:36.463

11

try 
{
    $cnx = new PDO ($dsn,$dbuser,$dbpass);   
    $cnx->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $cnx->beginTransaction ();

    $stmt = $cnx->prepare ("SELECT * FROM users WHERE username=?");
    $stmt->execute(array($username));

    $cnx->commit();

    while ($row = $stmt->fetch (PDO::FETCH_OBJ)){
        echo $row->userid;
    }
}

catch (Exception $e) { 
    if (isset ($cnx)) 
        $cnx->rollback ();
       echo "Error:  " . $e; 
    }
}

edited May 17 '12 at 20:42

answered May 17 '12 at 20:06

Jeroen

13,056
4
42
63

This, although there is a bit missing – Andrew Willis May 17 '12 at 20:09
1) if the PDO constructor throws, the catch block will cause a notice about an undefined variable. 2) swallowing the error message completely is not a good idea. In the real world, the actual message and stack trace should be logged. – DCoder May 17 '12 at 20:10
I didn't even look at the exception handling actually, but DCoder is absolutely right, that should be improved as well – Jeroen May 17 '12 at 20:10
yes but the question asked was not to do with error handling so your answer is acceptable – Andrew Willis May 17 '12 at 20:11
It's far from perfect, but I've improved the exception handling a little bit :) – Jeroen May 17 '12 at 20:13
Thanks a lot for your responses. I've been trying to get it to work. I edited my Post with the updated code. I get an error, for an unknown reason. Thanks a lot – alexx0186 May 17 '12 at 20:27
You miss one closing accolade } before the catch() function – Jeroen May 17 '12 at 20:29
(I've updated the code in my answer, it's your updated code with some fixes) – Jeroen May 17 '12 at 20:30
Thanks a lot for your help. It did solve my problem. There is another mistake I made: it's `echo $row->userid;` not `echo $stmt->userid;`. Regards – alexx0186 May 17 '12 at 20:42

score 1 · Answer 3 · answered May 17 '12 at 20:19

Did you mean this?

try {
    $cnx = new PDO($dsn,$dbuser,$dbpass);   
    $cnx->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $cnx->beginTransaction();
    $stmt=$cnx->prepare("
        SELECT * FROM users, othertable 
        WHERE users.username=? 
        AND othertable.some_column=?");

    $stmt->execute(array($user_input,$user_input_2));

    $cnx->commit();
}
catch (Exception $e){
       $cnx->rollback();
       echo "an error has occured";
}

That is assuming that the two tables data does not have duplicate field names, otherwise you're going to have to use:

SELECT users.field1 as u_field1, othertable.field1 as o_field1 FROM users, othertable 
WHERE users.username=? 
AND othertable.some_column=?

Hi, thanks for your response. I actually used two SELECT queries as an example, but I want to use UPDATE queries as well. Thanks, regards — alexx0186, May 17 '12 at 20:48
http://dev.mysql.com/doc/refman/5.0/en/join.html has examples of using joins for UPDATE / DELETE / SELECT queries — Andrew Willis, May 18 '12 at 16:10

How can I use prepared statements combined with Transactions with PHP?

Edit

3 Answers3

Linked