Working on an UPDATE query for an Oracle database. The field in question is of the type NCHAR(25), which accepts a 25 character UTF-8 byte string. My input values are in ASCII which should work no problem.
The following snippet uses the oci_bind_by_name function to escape the variable in the WHERE clause and insert into the placeholder variable :herp.
$sql = "UPDATE MYTABLE SET OPT = '1' WHERE FIELD = :herp";
$stmt = oci_parse($this->conn, $sql);
oci_bind_by_name($stmt, ":herp", $record['value'], -1, SQLT_CHR);
This next snippet does not use the oci_bind_by_name function and instead inserts the variable into the SQL statement unescaped (YOLO).
$sql = "UPDATE MYTABLE SET OPT = '1' WHERE FIELD = '".$record['value']."'";
$stmt = oci_parse($this->conn, $sql);
The first snippet does not work, while the second one works fine, i.e. the UPDATE statement succeeds every time on the second method while it fails every time on the first.
Both versions of the UPDATE should work. However when I use the oci_bind_by_name function for a few fields, somehow the variable is getting changed. (I am doing more rigorous error checking in the actual code).
What is going on here? How can I still use the oci_bind_by_name instead of just concatenating the variable directly into the SQL statement?
Per the developers:
Neither PHP OCI8 or PDO_OCI support NVARCHAR, NCHAR or NCLOB types.