Oracle tiene una facilidad de fundamental importancia que a veces suele llamarse variable binding, pero que es más comunmente conocido (también en otros DBMS como MySQL, SQL Server o Postgre) como prepared statements o "sentencias preparadas", por la forma en que se utiliza esto a nivel código.
Lo explicaré desde el punto de vista de Oracle, que por lo que conozco es bastante más completo (y me es más familiar) y luego comentaré brevemente cómo se puede implementar en otros sistemas como MySQL.
¿A qué llamamos prepared statements?
Un prepared statement es básicamente una consulta SQL que se define en forma genérica con el fin de ser reutilizado varias veces a lo largo de la ejecusión de un mismo programa. Como generalmente una query suele tener filtros por determinados campos, donde comparamos el valor de uno o más campos contra uno o más valores variables, lo que utilizamos al definir el prepared statement es definir variables dentro de la query.
Optimización de consultas
Veamos esto con un ejemplo.
Supongamos que tenemos una consulta para obtener los datos de un usuario a partir de su ID:
$sSql = "SELECT user_name, user_password, user_birthdate FROM user WHERE user_id = " . $iUserId;
Dejaremos de lado por un momento los problemas de seguridad de esta query para ver qué sucede cuando se ejecuta en Oracle. Al ejecutarse esa query, por ejemplo cuando la variable $iUserId es igual a 5, se enviará a Oracle la siguiente consulta:
SELECT user_name, user_password, user_birthdate FROM user WHERE user_id = 5
Oracle tomará la consulta, elaborará un plan de ejecusión (dependiendo de muchas variables como índices, claves primarias y foráneas, estadísticas específicas de la base de datos, etc.), ejecutará la query y devolverá los resultados. Adicionalmente, esta query será almacenada en una suerte de cache que tiene Oracle con las últimas queries ejecutadas (no recuerdo el número exacto de consultas que se cachean, pero por decir algo digamos que son 100), junto con su plan de ejecusión. De esta manera, cuando una misma query se ejecuta varias veces seguidas, Oracle no debe estar armando el plan de ejecusión cada vez.
Supongamos que, seguidamente, ingresa otro usuario al sistema y se ejecuta la query para obtener los datos del usuario con ID = 6. Se ejecutaría la siguiente consulta:
SELECT user_name, user_password, user_birthdate FROM user WHERE user_id = 6
Oracle repetiría los pasos que describimos recién: plan de ejecusión, ejecusión, devolución de los resultados. ¿Por qué no utiliza el cache del que hablabamos?, porque evidentemente para Oracle las dos queries son diferentes: una tiene un 5 y la otra un 6. El query string, digamos, es diferente por lo tanto Oracle no tiene por qué pensar que el plan de ejecusión va a ser el mismo.
¿Cómo hacemos para que Oracle sepa que la query es la misma? Definimos ese $iUserId que usamos en PHP como una variable que pueda reconocer Oracle. Para ello, de la misma forma que en cualquier lenguaje, deberemos definir un nombre para la variable, ubicarla dentro de la query y luego asignarle un valor a esa variable. Dentro de la consulta SQL, las variables se identifican porque comienzan con dos puntos ":". La consulta que teníamos quedaría así:
$sSql = "SELECT user_name, user_password, user_birthdate FROM user WHERE user_id = :iUserId";
Luego, asignamos el valor a la variable en un array.
$aBindings = array ("iUserId" => $iUserId);
Por último, ejecutamos la query con nuestra clase de base de datos:
$aResult = $oDB->sql_query($sSql, $aBindings);
En pruebas en producción, con queries sobre tablas con cientos de miles de registros, creanmé que las diferencias de preformance son notables.
Consultas más seguras
Pero esto tiene otra ventaja adicional, en relación a un problema cotidiano de cualquier desarrollador, y sobre todo de cualquier desarrollador web: el escapado de las variables. Muchos freaks recordarán la famosa viñeta de xkcd al respecto.
El iUserId (o cualquier otra variable que usemos para filtrar), en muchos casos vendrá de un dato introducido por el usuario y debemos asegurarnos de validar esa información correctamente para evitar riesgos de SQL Inyection, es decir que en esa variable se introduzca código SQL que pueda modificar (en algunos casos muy perjudicialmente, HolaCine, cof, cof) el comportamiento esperado de la consulta. Ahora bien, yendo a la lógica más naif: ¿Si $iUserId es una variable que utilizo para filtrar, por qué le voy a meter SQL? Claro, hasta hace un rato Oracle no tenía ni idea que iUserId era una variable porque a él le llegaba el string de SQL entero y el ejecutaba.
Con los bindings Oracle sabe que :iUserId es una variable (de hecho, al momento de bindear con el statement se le puede indicar de qué tipo de dato es esa variable), por lo tanto no hay ninguna razón para evaluarlo como SQL. Nosotros podríamos tener:
$sSql = "SELECT user_name, user_password, user_birthdate FROM user WHERE user_id = :iUserId";
$aBindings = array ("iUserId" => '"" OR 1 = 1; DROP TABLE user;');
$aResult = $oDB->sql_query($sSql, $aBindings);
Y no vamos a tener ningún problema de inyection. Ojo, en ese caso en particular lo que sí vamos a tener es un error de Oracle diciendo que el valor que le pusimos a iUserId no es del tipo de dato adecuado (a menos que seamos tan nardos de poner un ID en un campo char/varchar).
Además, con los bindings no necesitamos preocuparnos por andar escapando comillas, porque el SQL statement y los valores viajan por canales separados y no como un string concatenado.
Cómo implementar prepared statements
Hasta ahora, a lo largo del artículo, utilicé siempre una ficticia clase de base de datos pero no dije cómo funcionaba internamente. Creo que no tiene sentido que reproduzca aquí la clase que uso, porque no es mía, tiene algunos problemas y seguramente ustedes podrán hacer alguna mejor. Voy a reproducir un pequeño ejemplo de cómo hacer una query con bindings usando la librería Oci8. Luego también está la alternativa con PDO que en todo caso veré para la próxima si armo algún ejemplo.
// Este sería el valor con el que vamos a filtrar
$mValue = 'some value';
// El prefetch define la cantidad de registros para la cual la librería alocará
// memoria inicialmente, en cada "ida" a buscar información.
// 200 suele ser un número recomendable cuando la query retornará varios
// registros.
$iPreFetch = 200;
// El array donde guardaremos la respuesta
$aResponse = array();
// Nos conectamos
$rConn = ocilogon($sDbUsername, $sDbPassword, $sDbName);
if ($rConn) {
// Armamos la query
$sSql = "SELECT field_1, field_2 FROM table WHERE field_3 = :value";
// Definimos el array de bindings
$aBindings = array (
'value' => $mValue
);
// Generamos el statement
$oStmt = ociparse($rConn, $sSql);
// Asociamos los bindings
foreach ($aBindings as $sWildcard => $sValue) {
@ocibindbyname($oStmt, ":" . $sWildcard, $sValue, -1);
}
// Vemos si hubo algun error al generar el statement
$err = ocierror($oStmt);
if(!$err) {
// Seteamos el prefetch
ocisetprefetch($oStmt, $iPreFetch);
// Ejecutamos el statement
$bOk = ociexecute($oStmt, OCI_DEFAULT);
// Vemos si hubo algún error en la ejecusión
$err = ocierror($oStmt);
// Si estuvo todo bien armamos el record set
if ($bOk) {
while(ocifetchinto($oStmt, $aRow, OCI_ASSOC+OCI_RETURN_NULLS+OCI
_RETURN_LOBS+OCI_NUM)) {
$aResponse[] = $aRow;
}
print_r($aResponse);
} else {
die("SQL error");
}
// Liberamos la memoria del statement
ocifreestatement($oStmt);
}
}
?>
$aResponse[] = $aRow;
}
print_r($aResponse);
} else {
die("SQL error");
}
// Liberamos la memoria del statement
ocifreestatement($oStmt);
}
}
?>
En otros DBMS
Otros DBMS como MySQL o SQL Server también soportan queries con bindings. Actualmente, la mejor forma que he visto es con PDO porque lo maneja de la misma forma que con Oracle. Para MySQL desde PHP también puede hacerse con la clase Mysqli, el problema es que en vez de poner nombres de variables en la query uno debe usar signos de pregunta "?", lo cual trae varios problemas: si una variable se usa más de una vez en una query se debe enviar repetidas veces; los valores de las variables deben ser enviados en orden; la comprensibilidad de la query en sí resulta más complicada.
Al implementarlo de la misma forma que en Oracle, con PDO se resuelve este problema.
Entradas
