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

Can you post a complete snippet please? I'm missing the code that creates the SQL string ($query) and the binding part. — Mureinik
– Mureinik, Commented Mar 5, 2015 at 7:13

timclutton · Accepted Answer · 2015-03-12 13:14:55Z

1

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

edited Mar 12, 2015 at 13:14

answered Mar 12, 2015 at 9:14

timclutton

13.1k3 gold badges37 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Slimshadddyyy Over a year ago

timclutton: $result = oci_execute($stmt);echo $result;. The output is 1

timclutton Over a year ago

Yes, because the return value from oci_execute is bool. What do you expect $result to contain?

Slimshadddyyy Over a year ago

timclutton:COMMENT is a reserved word in Oracle. Changing it to MYCOMMENTS works though. Also if I have only one variable to bind, do I need to loop or just use oci_bind_by_name($stmt,":MYCOMMENTS",$comments); ?

timclutton Over a year ago

If there are no params in the query (e.g. select * from dual) then passing an empty array will cause the loop not to execute and nothing to bind. Even better would be to make the parameter optional (function customExecute($sql, $params = [])); then you can omit the param from the call.

Slimshadddyyy Over a year ago

Upvoted and accepted. And the one i was curious about stackoverflow.com/questions/29002992/…

|

Rob G · Accepted Answer · 2015-03-05 08:43:25Z

0

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

answered Mar 5, 2015 at 8:43

Rob G

6125 silver badges18 bronze badges

3 Comments

Slimshadddyyy Over a year ago

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

mHouses Over a year ago

You don't need to pass the $stmt from your singleton. The statement is generated every time you prepare a query (the $dbh->prepare($query); line). All you need is to access the $dbh (database handler) from your code. Anyway, this code is using PDO, and the Oracle PDO drives isn't the reccomended way to access an Oracle DB (It's marked as experimental: php.net/manual/en/ref.pdo-oci.php)

Rob G Over a year ago

Agreed. As others have said, I would use OCI rather than PDO for Oracle. Use an array if you need to pass in multiple bind parameters & their values

mHouses · Accepted Answer · 2015-03-05 08:45:12Z

0

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

answered Mar 5, 2015 at 8:45

mHouses

9191 gold badge17 silver badges37 bronze badges

1 Comment

Slimshadddyyy Over a year ago

mHouses: The correct way is mentioned in manual but how can I modify according to above code structure ?

Collectives™ on Stack Overflow

Insert Data in Oracle DB using PHP

3 Answers 3

10 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related