CODEWITHSUNDEEP
phporacle-databaseplsqloci8
Gabsii
Gabsii

Reputation: 456

php application raises ORA-00903 although the table is present

I am trying to program a PHP Web Application, for this application I do need a funtction to get the ID of a table and since I didnt want to work with an endlessly long switch i thought I would make the following function:

function getId($conn, string $col, string $tbl, string $val): int{
  $qry = oci_parse($conn, "SELECT :col FROM :tbl WHERE :col = :val");
  oci_bind_by_name($qry, ':tbl', $tbl);
  oci_bind_by_name($qry, ':col', $col);
  oci_bind_by_name($qry, ':val', $val);
  @$success = oci_execute($qry);

  if($success){
    //fetch data
  }else {
    $err = oci_error($qry);
    print_r($err);
    }
  }
}

I first thought it was because it wouldnt bind my variables, but I tested it and it returns true so binding seems to work. If I try to use the function i get the following error message:

Array
(
    [code] => 903
    [message] => ORA-00903: Invalid table name
    [offset] => 20
    [sqltext] => SELECT :col FROM HR.:tbl WHERE :col = :val;
)

I already read through some posts and I still cant find my error. I hope you can help me.

Upvotes: 1

Views: 127

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

As far as I know, it is not possible to bind the table and column names in a prepared statement. This isn't really a limitation on prepared statements, since in general the column and table structure would be different across different tables, and hence the same statements could not be reused anyway.

So I expect the following will work:

$qry = oci_parse($conn, "SELECT some_col FROM your_table WHERE some_col = :val");
oci_bind_by_name($qry, ':val', $val);
@$success = oci_execute($qry);

Internally I am also not sure what actually is happening here, but one possibility is that PHP is escaping your table name as a parameter. In any case, it is not leading to a query which will run.

If you need to change the columns or table in a prepared statement, just go ahead and create another statement.

Upvotes: 1

Related Questions