Insert Data in Oracle DB using PHP

Question

Inserting data in oracle DB using oci_8. Sample query to insert string with special characters or quotes

 update TABLENAME set COMMENTS = 'As per Mark's email dated 28-Feb-2015 - Bill Gates & Team's effort' where ID = 99;

To insert/update

$query = 'update TABLENAME set COMMENTS = '$_POST[comments]';

$result = customexecute($new_query);

public function customexecute($query)
{

    $resutlt = parent::customquery($query);
    return $resutlt;
}


public static function customquery($query)
{

  try{

        $stmt = oci_parse($conn, $query);
        oci_execute($stmt,OCI_COMMIT_ON_SUCCESS);
        oci_commit(db_singleton::getInstance());
        oci_free_statement($stmt);
        }catch (Exception  $e)
        {
            print_r($e);
        }

    }

Executing it on ORACLE DB it says SQl command not properly ended. Looked into Parameterized queries mentioned here but not able to integrate it succesfully.

$query = 'UPDATE tablename SET field = :field WHERE id = :id';
$stmt = oci_parse($oracleConnection, $query);
oci_bind_by_name($stmt, ':field', "The field value with 'apostrophes' and so");
oci_bind_by_name($stmt, ':id', '125');
$result = oci_execute($stmt);

I can pass :bind_comments in my query which is in my controller. But $stmt resides in my db_singleton file (general for all DB queries) and can not pass seperately for a individual query.

How can I sanitize user input or do not allow data to be used in creating SQL code

Answer 1

No, unsurprisingly, MySQL functions won't work with Oracle DB :)

You need to parameterise things, e.g.:

$query = 'update TABLENAME set COMMENTS = :bind_comments where id = :bind_id';
$stmt = $dbh->prepare($query);
$stmt->bindParam(':bind_comments', $_POST['comments']);
$stmt->bindParam(':bind_id', $_POST['id']);

$stmt->execute();

Answer 2

The correct way of using the OCI8 PHP extensions is:

$query = 'UPDATE tablename SET field = :field WHERE id = :id';
$stmt = oci_parse($oracleConnection, $query);
oci_bind_by_name($stmt, ':field', "The field value with 'apostrophes' and so");
oci_bind_by_name($stmt, ':id', '125');
$result = oci_execute($stmt);

More information: http://php.net/manual/book.oci8.php

Answer 3

From the update function, pass everything needed to the execute function:

$result = customExecute(
    'update xxx set comments=:COMMENTS where id=:ID',
    [
        ':COMMENTS' => $_POST['comment'],
        ':ID' => 99
    ]
);

Then in the execute function simply iterate the array to bind all params:

public static function customExecute($sql, array $params = [])
{
    $stmt = oci_parse($conn, $sql);
    foreach ($params as $key => &$value) {
        oci_bind_by_name($stmt, $key, $value);
    }
    $result = oci_execute($stmt);
    ...
}