title: How can I use prepared statements combined with Transactions with PHP?
body: <p>My goal is to use a transaction and a prepared statement simultaneously, to achieve both integrity of data, and prevention of SQL injection.</p>
  
  <p>I have this:</p>
  
  <pre><code>   try {
          $cnx = new PDO($dsn,$dbuser,$dbpass);   
          $cnx-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  
          $cnx-&gt;beginTransaction();
          $cnx-&gt;query("SELECT * FROM users WHERE username=$escaped_input");
          $cnx-&gt;query("SELECT * FROM othertable WHERE some_column=$escaped_input_2");
  
          $cnx-&gt;commit();
      }
  
      catch (Exception $e){
             $cxn-&gt;rollback();
             echo "an error has occured";
  
      }
  </code></pre>
  
  <p>I would like to incorporate the query as one would with a prepared statement:</p>
  
  <pre><code>$stmt=$cxn-&gt;prepare("SELECT * FROM users WHERE username=?");
  $stmt-&gt;execute(array($user_input));
  
  $stmt_2=$cxn-&gt;prepare("SELECT * FROM othertable WHERE some_column=?");
  $stmt_2-&gt;execute(array($user_input_2));
  </code></pre>
  
  <p>How can I achieve that?</p>
  
  <h2>Edit</h2>
  
  <p>I get this error:</p>
  
  <blockquote>
    <p>PHP Parse error:  syntax error, unexpected T_CATCH</p>
  </blockquote>
  
  <p>Here is my updated code:</p>
  
  <pre><code>try 
  {
      $cnx = new PDO($dsn,$dbuser,$dbpass);   
      $cnx-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  
      $cnx-&gt;beginTransaction();
      $stmt=$cnx-&gt;prepare("SELECT * FROM users WHERE username=?");
      $stmt-&gt;execute(array($username));
  
      $cnx-&gt;commit();
  
      while ($row=$stmt-&gt;fetch(PDO::FETCH_OBJ)){
      echo $stmt-&gt;userid;
  
  }
  
  catch(Exception $e) { 
      if (isset($cnx)) 
          $cnx-&gt;rollback();
         echo "Error:  " . $e; 
      }
  </code></pre>