如何将准备好的语句与PHP事务结合使用? [英] How can I use prepared statements combined with Transactions with PHP?

问题描述

我的目标是同时使用事务和准备好的语句，以实现数据完整性和防止SQL注入.

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

我有这个:

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

我该如何实现?

我收到此错误:

PHP解析错误:语法错误，意外的T_CATCH

PHP Parse error: syntax error, unexpected T_CATCH

这是我更新的代码:

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

推荐答案

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

这篇关于如何将准备好的语句与PHP事务结合使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！