[FIXED] How to use prepared statements and bound parameters in PHP oci8

Issue

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.

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

    ?>

Solution

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

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.

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.

Read more here.

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

    ?>

Answered By - csandreas1

Answer Checked By - Timothy Miller (PHPFixing Admin)