Web and Beer

¿Qué son los bindings?

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

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.

Antes que nada, una pequeña presentación. Mi nombre es Andrés, actualmente trabajo en Intraway, y Cesar me pidió que escribiera algunas cositas sobre Oracle.

Debo decir, para empezar que no soy ningún especialista en el tema, y que seguramente cualquier otro compañero podría decirles muchas más cosas sobre este enorme y complejo DBMS. Por lo tanto, me pareció que una buena aproximación para escribir al respecto podría ser comentar las cosas más importantes que fui aprendiendo en el último año y medio que vengo trabajando con Oracle (particularmente Oracle 10g). No son necesariamente cuestiones específicas de Oracle, sino que yo las aprendí trabajando sobre ese entorno y frecuentemente son cosas que los desarrolladores web no tomamos en cuenta. Así doy comienzo a esta pequeña serie de posts llamada “Oracle/PHP for starters”.

Antes que nada, un pequeño comentario sobre cómo trabjar con Oracle desde PHP (que es lo que yo uso).  Actualmente, PHP incluye dos librerías para trabajar con Oracle (en realidad hay más, pero estas son las más importantes): Oci8 y PDO. La primera es una librería en C específica para conectarse a Oracle y la segunda son los PHP Data Objects que sirven de interfaz para conectarse a múltiples sistemas de bases de datos. Si bien yo diriamente trabajo con Oci8, al día de hoy me parece que lo más adecuado es incorporar PDO para obtener una aproximación uniforme a múltiples DBMS.

Adicionalmente, es muy recomendable este PDF de Oracle, llamado “The Underground PHP and Oracle Manual“, que se actualiza regularmente y explica todo lo que uno debería saber sobre cómo trabajar con Oracle desde PHP. De hecho, abunda más en las distintas formas de conectarse a Oracle desde PHP con Oci8, PDO, ADOdb, PEAR DB, PEAR MDB2, y Zend Core for Oracle.

Voy a empezar con un tema muy (quizás demasiado) sencillo, pero que a la hora de la verdad, hace la diferencia en términos de rendimiento y seguridad.

Olvidate de los asteriscos

Empecemos por el más sencillo de todos. Evitar a toda costa los asteriscos (*) en las consultas. Este detalle, que para muchos será absolutamente básico (casi me avergüenza tener que mencionarlo), no lo es tanto para la mayoría de los desarrolladores. ¿Cuántas sencillas aplicaciones de ABM en PHP con MySQL hemos visto que hacen queries del estilo de esta?:

SELECT * FROM tabla;

Pero, ¿por qué debemos evitar este tipo de prácticas?. Muy sencillo: con el * le estamos diciendo al DBMS que traiga todos los campos de la/s tabla/s seleccionadas, con lo cual el motor pierde una cantidad preciosa de tiempo en el plan de ejecusión buscando la estructura de cada tabla para poder devolvérnosla. Ahora bien, si nosotros ya conocemos la estructura de la tabla, ¿por qué no se la adelantamos al motor para que se ahorre ese paso?. Es un detalle básico, que puede demorarnos unos segundos extra al momento de armar la query y nos puede llegar a ahorrar mucho tiempo después en la performance de la query. Solamente basta reescribir la query para dejar algo como esto:

SELECT campo1, campo2, campo3 FROM tabla;

Adicionalmente, deberíamos agregar que el orden de los campos incide en la performance de la query, por lo que es importante siempre procurar escribirlos en el orden en que se encuentran en la tabla. De la misma forma, si la consulta incluye joins con otras tablas, los campos deberán ser solicitados en el orden en que se consultan las tablas.

La misma idea sería aplicable al caso de los COUNT(*). ¡He llegado a encontrarme casos de gente que hace un ‘SELECT *’ y luego un count() en PHP para saber cuántos registros hay en una tabla!. Más allá de esos casos extremos, sería bueno reemplazar queries como la siguiente:

SELECT COUNT(*) FROM tabla;

Por alguna de estas otras:

SELECT COUNT(tabla_id) AS qty FROM tabla;

SELECT COUNT(1) AS qty FROM tabla;

package {
import pl.mooska.asql.*;
public class ASQLTest extends Sprite
{
private var conn:Asql = new Asql();
public function ASQLTest()
{
conn.addEventListener(SQLEvent.CONNECT, handleConnect);
conn.addEventListener(SQLEvent.SQL_DATA, getList);
conn.connect(“urldb”,”user”,”pass”,”schema”,3306);
}
private function handleConnect ( evt:SQLEvent ) :void
{
conn.query(“SELECT * FROM list”);
}
private function getList ( evt:SQLEvent ) :void
{
trace(“data > ” + evt.data.toString());
conn.disconnect();
}
}
}

Pablo Morales

hacer una conexion a un mysql y devolver un array con los registros con Zend Framework (php)

$db = new Zend_Db_Adapter_Pdo_Mysql

Hola gente como andan????.

Hoy vamos a hablar de sphinx, una solucion interezante a la hora de solucionar el problema de la busquedas de texto.

Normalmente sacrificamos el modelo de entidad/relacion e integridad referencial con alguna tabla MyISAM (en MySQL claro esta) para aplicar un full-text y hacer que las busquedas de texto sean mas rapidas.

La verdad, eso es una verdadera porqueria, y todos lo sabemos.

Ahora bien, unos simpaticos rusos nos dieron una solucion llamada Sphinx, que es un programa que toma un query SQL, indexa el resultado, y despues nos deja un indece propio para las busquedas de texto.

Por ejemplo, tenemos una tabla de users con mas de 1 millon de registros, y queremos buscar users por username, email, nombre y apellido.

En Sphinx creamos un indice que se alimentara del query

select username, password, email, fname, lname from Users;

Y le indicamos que el “id” sera el user_id.

Sphinx corre el query intermamente, y armar el indexe de texto.

Las busquedas con Sphinx son de lo mas rapida que se vio, asi que les sorprendera.

Trae una API para PHP (para usarlo desde PHP) y hacer las busquedas.

Tiene una gran varidad de parametros para la relevancia de la busqueda y cosas asi, les aseguro que es ideal.

En proximos post se hablare de ejemplos, casos de implementacion, tips, etc.