oci_bind_by_name
resource$statement,
string$param,
mixed
[int$max_length = -1],
[int$type = 0]
): bool
Binds a PHP variable var to the Oracle bind variable placeholder param. Binding is important for Oracle database performance and also as a way to avoid SQL Injection security issues.
Binding allows the database to reuse the statement context and caches from previous executions of the statement, even if another user or process originally executed it. Binding reduces SQL Injection concerns because the data associated with a bind variable is never treated as part of the SQL statement. It does not need quoting or escaping.
PHP variables that have been bound can be changed and the statement re-executed without needing to re-parse the statement or re-bind.
In Oracle, bind variables are commonly divided into IN binds for values that are passed into the database, and OUT binds for values that are returned to PHP. A bind variable may be both IN and OUT. Whether a bind variable will be used for input or output is determined at run-time.
You must specify max_length when using an OUT bind so that PHP allocates enough memory to hold the returned value.
For IN binds it is recommended to set the max_length length if the statement is re-executed multiple times with different values for the PHP variable. Otherwise Oracle may truncate data to the length of the initial PHP variable value. If you don't know what the maximum length will be, then re-call oci_bind_by_name with the current data size prior to each oci_execute call. Binding an unnecessarily large length will have an impact on process memory in the database.
A bind call tells Oracle which memory address to read data from. For IN binds that address needs to contain valid data when oci_execute is called. This means that the variable bound must remain in scope until execution. If it doesn't, unexpected results or errors such as "ORA-01460: unimplemented or unreasonable conversion requested" may occur. For OUT binds one symptom is no value being set in the PHP variable.
For a statement that is repeatedly executed, binding values that never change may reduce the ability of the Oracle optimizer to choose the best statement execution plan. Long running statements that are rarely re-executed may not benefit from binding. However in both cases, binding might be safer than joining strings into a SQL statement, as this can be a security risk if unfiltered user text is concatenated.
Parameters
- statement
-
A valid OCI8 statement identifier.
- param
-
The colon-prefixed bind variable placeholder used in the statement. The colon is optional in param. Oracle does not use question marks for placeholders.
- var
-
The PHP variable to be associated with param
- max_length
-
Sets the maximum length for the data. If you set it to -1, this function will use the current length of var to set the maximum length. In this case the var must exist and contain data when oci_bind_by_name is called.
- type
-
The datatype that Oracle will treat the data as. The default type used is SQLT_CHR. Oracle will convert the data between this type and the database column (or PL/SQL variable type), when possible.
If you need to bind an abstract datatype (LOB/ROWID/BFILE) you need to allocate it first using the oci_new_descriptor function. The length is not used for abstract datatypes and should be set to -1.
Possible values for type are:
SQLT_BFILEE or OCI_B_BFILE - for BFILEs;
SQLT_CFILEE or OCI_B_CFILEE - for CFILEs;
SQLT_CLOB or OCI_B_CLOB - for CLOBs;
SQLT_BLOB or OCI_B_BLOB - for BLOBs;
SQLT_RDD or OCI_B_ROWID - for ROWIDs;
SQLT_NTY or OCI_B_NTY - for named datatypes;
SQLT_INT or OCI_B_INT - for integers;
SQLT_CHR - for VARCHARs;
SQLT_BIN or OCI_B_BIN - for RAW columns;
SQLT_LNG - for LONG columns;
SQLT_LBI - for LONG RAW columns;
SQLT_RSET - for cursors created with oci_new_cursor;
SQLT_BOL or OCI_B_BOL - for PL/SQL BOOLEANs (Requires OCI8 2.0.7 and Oracle Database 12c)
Return Values
Returns true on success or false on failure.
Notes
Warning:
Do not use addslashes and oci_bind_by_name simultaneously as no quoting is needed. Any magically applied quotes will be written into your database because oci_bind_by_name inserts data verbatim and does not remove quotes or escape characters.
Note:
If you bind a string to a CHAR column in a WHERE clause, remember that Oracle uses blank-padded comparison semantics for CHAR columns. Your PHP variable should be blank padded to the same width as the column for the WHERE clause to succeed.
Note:
The PHP var argument is a reference. Some forms of loops do not work as expected:
<?php
foreach ($myarray as $key => $value) {
oci_bind_by_name($stid, $key, $value);
}
?>
This binds each key to the location of $value, so all bound variables end up pointing to the last loop iteration's value. Instead use the following:
<?php
foreach ($myarray as $key => $value) {
oci_bind_by_name($stid, $key, $myarray[$key]);
}
?>