预处理语句和存储过程

    许多成熟的数据库都支持预处理语句的概念。它们是什么？可以将它们视为应用程序要运行的 SQL 的一种编译模板，可以使用变量参数进行自定义。预处理语句提供了两大优势

    • 查询只需要解析（或准备）一次，但可以使用相同或不同的参数执行多次。当查询准备就绪时，数据库将分析、编译和优化其执行查询的计划。对于复杂的查询，此过程可能需要足够的时间，以至于如果需要使用不同的参数重复执行相同的查询，则会明显降低应用程序的速度。通过使用预处理语句，应用程序避免了重复分析/编译/优化循环。这意味着预处理语句使用更少的资源，因此运行速度更快。
    • 预处理语句的参数不需要加引号；驱动程序会自动处理此操作。如果应用程序专门使用预处理语句，开发人员可以确保不会发生 SQL 注入（但是，如果查询的其他部分是用未转义的输入构建的，SQL 注入仍然有可能发生）。

    预处理语句非常有用，以至于它们是 PDO 唯一为不支持它们的驱动程序模拟的功能。这确保应用程序能够使用相同的数据库访问范式，无论数据库的功能如何。

    示例 #1 使用预处理语句重复插入

    此示例通过为命名占位符替换 name 和 value 来执行 INSERT 查询。

    <?php
    $stmt     = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':value', $value);

    // 插入一行
    $name = 'one';
    $value = 1;
    $stmt->execute();

    // 插入另一行，使用不同的值
    $name = 'two';
    $value = 2;
    $stmt->execute();
    ?>

    示例 #2 使用预处理语句重复插入

    此示例通过为位置 ? 占位符替换 name 和 value 来执行 INSERT 查询。

    <?php
    $stmt     = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
    $stmt->bindParam(1, $name);
    $stmt->bindParam(2, $value);

    // 插入一行
    $name = 'one';
    $value = 1;
    $stmt->execute();

    // 插入另一行，使用不同的值
    $name = 'two';
    $value = 2;
    $stmt->execute();
    ?>

    示例 #3 使用预处理语句获取数据

    此示例根据表单提供的键值获取数据。用户输入会自动加引号，因此没有发生 SQL 注入攻击的风险。

    <?php
    $stmt     = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
    $stmt->execute([$_GET['name']]);
    foreach (    $stmt as $row) {
    print_r($row);
    }
    ?>

    示例 #4 使用输出参数调用存储过程

    如果数据库驱动程序支持，应用程序也可以绑定输出参数以及输入参数。输出参数通常用于从存储过程检索值。输出参数的使用比输入参数稍微复杂一些，因为开发人员必须知道绑定参数时给定参数可能有多大。如果值的大小最终大于建议的大小，则会引发错误。

    <?php
    $stmt     = $dbh->prepare("CALL sp_returns_string(?)");
    $stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);

    // 调用存储过程
    $stmt->execute();

    print     "procedure returned $return_value\n";
    ?>

    示例 #5 使用输入/输出参数调用存储过程

    开发人员还可以指定既包含值又包含输出的值的参数；语法类似于输出参数。在下一个示例中，字符串“hello”被传递到存储过程，当它返回时，“hello”被存储过程的返回值替换。

    <?php
    $stmt     = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
    $value = 'hello';
    $stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000);

    // 调用存储过程
    $stmt->execute();

    print     "procedure returned $value\n";
    ?>

    示例 #6 占位符的无效使用

    <?php
    $stmt     = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
    $stmt->execute([$_GET['name']]);

    // 占位符必须用于整个值的位置
    $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
    $stmt->execute(["%$_GET[name]%"]);
    ?>

    改进此页面

    了解如何改进此页面 • 提交拉取请求 • 报告错误
    ＋添加注释

    用户贡献的注释 4 notes

    220
    adam at pyramidpower dot com dot au
    14 年前
    请注意，当使用 bindParam 的名称参数时，名称本身不能包含连字符“ -”。

    示例
    <?php
    $stmt     = $dbh->prepare ("INSERT INTO user (firstname, surname) VALUES (:f-name, :s-name)");
    $stmt -> bindParam(':f-name', 'John');
    $stmt -> bindParam(':s-name', 'Smith');
    $stmt -> execute();
    ?>

    'f-name' 和 's-name' 中的连字符应替换为下划线或根本不使用连字符。

    见 http://bugs.php.net/43130

    亚当
    1
    theking2(at)king.ma
    5 个月前
    在 MariaDB 上尝试示例 #5 时，会得到 1414。使用此函数调用存储过程，最后一个参数为 INOUT，返回像 (uu)id 或计数这样的值；

    <?php
    /**
    * call_sp 使用给定参数调用指定的存储过程。
    * 第一个参数是存储过程的名称。
    * 其余参数是存储过程的（in）参数。
    * 最后一个（out）参数应该是一个 int，如状态或受影响的行数。
    *
    * @param mixed $sp_name 要调用的存储过程的名称。
    * @param mixed $params 要传递给存储过程的参数。
    * @return int 受影响的行数。
    */
    function call_sp( \PDO $db, string $sp_name, ...$params ): mixed
    {
    $placeholders = array_fill( 0, count( $params ), "?" );
    $placeholders[] = "@new_id";

    $sql = "CALL $sp_name( " . implode( ", ", $placeholders ) . " ); SELECT @new_id AS `new_id`";

    try {
    LOG->debug( "调用存储过程", [ "sql" => $sql ] );

    $stmt = $db->prepare( $sql );
    $i = 0;
    foreach(     $params as $param ) {
    $stmt->bindValue( ++$i, $param );
    }
    $stmt->execute();
    $new_id = $stmt->fetch( PDO::FETCH_ASSOC )['new_id'];

    return     $new_id;

    } catch (     \Exception $e ) {
    LOG->error( "调用存储过程出错", [ "sql" => $sql, "params" => $params, "error" => $e->getMessage() ] );
    throw     $e;
    }
    10
    w37090 at yandex dot ru
    4 年前
    通过准备好的查询将多维数组插入数据库
    我们有一个数组来写入表单

    $dataArr
    数组
    (
    [0] => 数组
    (
    [0] => 2020
    [1] => 23
    [2] => 111111
    )

    [1] => 数组
    (
    [0] => 2020
    [1] => 24
    [2] => 222222222
    )
    ....

    任务：准备请求并通过绑定传递
    $array = [];
    foreach ($dataArr as $k=>$v) {
    // $x = 2020，变量是预先确定的，不会改变本质
    $array[] = [$x, $k, $v];
    }
    $sql = ("INSERT INTO `table` (`field`,`field`,`field`) VALUES (?,?,?)");

    $db->queryBindInsert($sql,$array);

    public function queryBindInsert($sql,$bind) {
    $stmt = $this->pdo->prepare($sql);

    if(count($bind)) {
    foreach($bind as $param => $value) {
    $c = 1;
    for ($i=0; $i<count($value); $i++) {
    $stmt->bindValue($c++, $value[$i]);
    }
    $stmt->execute();
    }
    }
    }
    -61
    bkilinc at deyta dot net
    3 年前
    最好不要在 sql 字符串中使用双引号。这样可以确保没有变量被注入查询中。
    带有参数的简单查询应该是；
    'INSERT INTO REGISTRY (name, value) VALUES (?, ?)'
    而不是
    "INSERT INTO REGISTRY (name, value) VALUES (?, ?)"
    ＋添加注释
    To Top