PHP: How to Insert records in Oracle db using parameters?

Question

I am trying to insert records into an Oracle database using PHP, but for some reason, the INSERT code is not working.

--DB Table
CREATE TABLE USER (
    ID NUMBER(20) GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),  
    ROLE NUMBER(10),
    NAME VARCHAR2(50),
    PASSWORD VARCHAR2(50),
);

The connection string works:

#PHP: New Oracle Database Connection (db.php):
<?php
$user = "test";
$password = "test123";
$host = "localhost/XE";
$connection = oci_connect($user, $password, $host)
or die(oci_error());
if(!$connection){
    echo "Failed: Verify the connection string in db.php";
}else{
    //echo "CONNECTED";
}
oci_close ($connection);
?>

This is the insert in PHP that does not work, Could you help me to identify the error?

#PHP+Oracle: INSERT new user (userControllerOracle.php)
<?php
#DB Connection:
include("db.php");
#DB Oracle INSERT:
if (isset($_POST['registro'])) {
    $name       = $_POST['name'];
    $password   = $_POST['password'];
    $cnn = oci_connect($user, $password, $host);
    $query = "INSERT INTO USER (1, name, password) VALUES (1,'$name', '$password')";
}
?>

Previously, I used MySQL and it worked, but I don't know how to achieve the same result in Oracle

 #PHP: MySQL Database Connection (db.php):
    <?php
    $con = mysqli_connect("localhost","test","test123","test");
    if (mysqli_connect_errno())
      {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }
      function debug_to_console($data) {
            $output = $data;
            if (is_array($output))
                $output = implode(',', $output);    
            echo "<script>console.log('Debug Objects: " . $output . "' );</script>";
        } 
    ?>

#PHP+MySQL: INSERT new user (userControllerMySQL.php)
<?php
#DB Connection:
include("php/db.php");
#DB MySQL INSERT:
if (isset($_POST['registro'])) {
    if (strlen($_POST['name']) >= 1 && strlen($_POST['password']) >= 1) {
        $name       = trim($_POST['name']);
        $password   = trim($_POST['password']);     
        $query      = "INSERT INTO USER(1, name, password) VALUES (1,'$name', MD5('$password')";
        $result     = mysqli_query($con,$query);
        if ($result) {
            ?> 
            <h5 style="text-align: center; background-color:#28a745; color:white">User added!</h5>
           <?php
        } else {
            ?> 
            <h5 style="text-align: center; background-color:#dc3545; color:white">error!</h5>
           <?php
        }
    }   else {
            ?> 
            <h5 style="text-align: center; background-color:#ffc107; color:#343a40">add all fields!</h5>
           <?php
    }
}
?>

First rule of DB query building; never put vars directly into the query. For passwords; don't use MD5, google why! If you want to know how to bind using params; look at example 3 on https://www.php.net/manual/en/function.oci-execute.php — mic, Jul 18 '21 at 09:35
I don't get any error, but when reviewing the table the record just hasn't been inserted — Tana, Jul 18 '21 at 10:20
**Never store passwords in clear text or using MD5/SHA1!** Only store password hashes created using PHP's [`password_hash()`](https://php.net/manual/en/function.password-hash.php), which you can then verify using [`password_verify()`](https://php.net/manual/en/function.password-verify.php). Take a look at this post: [How to use password_hash](https://stackoverflow.com/q/30279321/1839439) and learn more about [bcrypt & password hashing in PHP](https://stackoverflow.com/a/6337021/1839439) — Dharman, Jul 18 '21 at 12:58

score 0 · Answer 1 · answered Jun 04 '22 at 00:20

Use oci_bind_by_name() to bind parameters in the SQL, rather than substituting variables directly into the SQL string.

$name       = $_POST['name'];
$password   = $_POST['password'];
$cnn = oci_connect($user, $password, $host);
$query = "INSERT INTO USER (1, name, password) VALUES (1,:name, :password)";
$stmt = oci_parse($conn, $query);
oci_bind_by_name($stmt, ':name', $name);
oci_bind_by_name($stmt, ':password', $password);
oci_execute($stmt);

score -1 · Answer 2 · edited Jun 04 '22 at 00:14

-1

Fixed, I was missing:

$parse = oci_parse($connection, $sql);
oci_execute($parse);

edited Jun 04 '22 at 00:14

lemon

14,875
6
18
38

answered Jul 18 '21 at 11:15

Tana

297
3
13

You're not using parameters. See the `oci_bind_by_name()` function for binding parameters. – Barmar Jun 04 '22 at 00:17

PHP: How to Insert records in Oracle db using parameters?

2 Answers2