CODEWITHSUNDEEP
phporacle-database
Daniel Nill
Daniel Nill

Reputation: 5747

Using oci_parse and oci_execute

I'm sure this is something very basic but I can't seem to find my error.

I'm trying to execute the following...

$c = db_connect();

$email = addslashes($email);

$sql = "SELECT * FROM RUSER WHERE email LIKE '" . $email . "';";
$query = oci_parse($c, $sql) or die(oci_error($c));
$response = oci_execute($query) or die(oci_error($c));

but I get oci8 statement Warning: oci_execute(): ORA-00911: invalid character in /path/to/file.php on line 67 where line 67 is where $response is assigned.

So that means there is something wrong with $query right? But I can't seem to find what that would be. The raw sql executes fine from the command line. echoing get_resource_type($query) gives a resource id...

What am I doing wrong?

Upvotes: 5

Views: 13237

Answers (3)

mickmackusa
mickmackusa

Reputation: 47864

Yes, the semicolon is an issue, but not the only one.

  • the query is directly injecting the variable string into the sql -- this is a potential point of vulnerability/insecurity.
  • there is no need for the LIKE comparison if you aren't using any wildcard characters (e.g. %, _) in your value.

Suggested Code:

$stmt = oci_parse($conn, "SELECT * FROM RUSER WHERE email = :email");
oci_bind_by_name($stmt, ":email", $email);
oci_execute($stmt);
$count = oci_fetch_all($stmt, $resultSet, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
// hypothetical outputs:
// $count = 1
// $resultSet = [['id => 3, 'email' => '[email protected]', ...]]

Upvotes: 1

Rajan
Rajan

Reputation: 196

The first error is

$c = oci_connect("user","password","host/dbname") // db_connect() is not true

second error is there should not be ";" in the statement 

$sql = "SELECT * FROM RUSER WHERE email LIKE '" . $email . "';";

it should be 

$sql = "SELECT * FROM RUSER WHERE email LIKE '" . $email . "'";

if you want to compare better user "=" than LIKE

Upvotes: 2

Sodved
Sodved

Reputation: 8588

Do NOT include the ; in your SQL. The ; is not part of SQL itself, its used by various SQL clients (e.g. sql*plus) as a delimiter to mark the end of commands to be sent to the server.

Upvotes: 14

Related Questions