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

mysql, pdo, php

Solution

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; 
    }
}

Problem

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; } ```

Original source