php strings don't like mutiple variable insertions?

Question

I'm using php 5.2 with oracle 11.1.

This code:

$query = oci_parse($conn, "SELECT * FROM COMMENTS WHERE PINID=$pinID and COMMENTID=$commentID");

results in this error:

<b>Warning</b>:  oci_execute() [<a href='function.oci-execute'>function.oci-execute</a>]: ORA-00904: &quot;COMMENTS&quot;: invalid identifier in <b>C:\IODwww\hello.php</b> on line <b>159</b><br />

^

But running this works fine:

$query = oci_parse($conn, "SELECT * FROM COMMENTS WHERE PINID=$pinID and COMMENTID=1");

If this a result of me injecting multiple variables into the query string, or am I making some other mistake?

Answer 1

Have you tried putting the variables within brackets?

$query = oci_parse($conn, "SELECT * FROM COMMENTS WHERE PINID={$pinID} and COMMENTID={$commentID}");

Also make sure that $commentID is not returning a blank value which would leave just COMMENTID= at the end and would cause an error when trying to run the query.

Answer 2

oci_execute()'s warning is not PHP warning. There is something wrong with resulting query.

print it out and take a look at it.

Answer 3

There is no problem with multiple variables in a php string.

The debug the Problem you can try

var_dump("SELECT * FROM COMMENTS WHERE PINID=$pinID and COMMENTID=$commentID");

and see if the output really matches

string(...) "SELECT * FROM COMMENTS WHERE PINID=1 and COMMENTID=1" 

the only things i can think of is that commentID is empty or contains a "\n" or something attached to it that causes the error.

The errorcode the database puts out "The column name entered is either missing or invalid." doesn't make much sense to me if works with =1

Answer 4

For both performance and SQL Injection reasons, you should be using placeholder variables, like so:

$query = oci_parse($conn, "SELECT * FROM COMMENTS WHERE PINID = :pinID and COMMENTID = :commentID");
oci_bind_by_name($query, ':pinID', $pinID, -1, SQLT_INT);
oci_bind_by_name($query, ':commentID', $commentID, -1, SQLT_INT);
oci_execute($query);