如何在PHP oci8中使用准备好的语句和绑定参数 [英] How to use prepared statements and bound parameters in PHP oci8

问题描述

因此，建议使用准备好的语句和绑定参数来编写sql语句. Oci8 手册没有描述如何使用准备好的语句.

So using prepared statements and bound parameters is the suggested way for writing sql statements. Oci8 manual does not describe how to do it with prepared statements.

下面是如何将查询的下一行作为对象返回，但这不是最佳实践，因为查询字符串可以包含where col = $PHPvariable

Below is how to return the next row from a query as an object, but it's not the best practice as the query string can contain a where col = $PHPvariable

<?php

    $conn = oci_connect('hr', 'welcome', 'localhost/XE');
    if (!$conn) {
        $e = oci_error();
        trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
    }

    $select_sql= oci_parse($conn, 'SELECT id, description FROM mytab');
    oci_execute($select_sql);

    while (($row = oci_fetch_object($select_sql)) != false) {
        // Use upper case attribute names for each standard Oracle column
        echo $row->ID . "<br>\n";
        echo $row->DESCRIPTION . "<br>\n"; 
    }

    oci_free_statement($stid);
    oci_close($conn);

    ?>

推荐答案

是的，可以对SQL语句使用oci8参数化查询.

Yes it's possible to use oci8 parameterized query for your sql statements.

oci_bind_by_name 将PHP变量绑定到Oracle绑定变量占位符bv_name.绑定对于Oracle数据库性能很重要，也是避免SQL注入安全问题的一种方式.

oci_bind_by_name binds a PHP variable to the Oracle bind variable placeholder bv_name. Binding is important for Oracle database performance and also as a way to avoid SQL Injection security issues.

绑定减少了SQL注入问题，因为从未将与绑定变量关联的数据视为SQL语句的一部分.它不需要引号或转义.

Binding reduces SQL Injection concerns because the data associated with a bind variable is never treated as part of the SQL statement. It does not need quoting or escaping.

在此处了解更多信息.

 <?php

    $conn = oci_connect("hr", "hrpwd", "localhost/XE");
    if (!$conn) {
        $m = oci_error();
        trigger_error(htmlentities($m['message']), E_USER_ERROR);
    }

    $sql = 'SELECT last_name FROM employees WHERE department_id = :dpid ';

    $stid = oci_parse($conn, $sql);
    $didbv = 60;

    oci_bind_by_name($stid, ':dpid ', $didbv);
    oci_execute($stid);

    while (($row = oci_fetch_object($stid)) != false) {
        echo $row->last_name ."<br>\n";
    }


    oci_free_statement($stid);
    oci_close($conn);

    ?>

这篇关于如何在PHP oci8中使用准备好的语句和绑定参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！