Raw SQL en Laravel 5.1

  • Por
Aprendemos a escribir consultas con SQL crudo, Raw SQL, en Laravel 5.1, el método de más bajo nivel para acceder a los datos mediante este framework PHP.

De entre todos los mecanismos que nos propone Laravel para acceso a datos, el de más bajo nivel es el que vamos a tratar en este artículo: "Raw SQL", que sería como escribir las consultas SQL "en crudo". En este tipo de acceso realizamos directamente el SQL de las consultas, que ejecutamos vía la clase BD que es una fachadas que nos ofrece Laravel.

Las consultas que escribes tendrán SQL puro, por lo que usarás el SQL propio del sistema gestor que estés utilizando en este momento. Otros mecanismos que veremos más adelante como Query Builder o el propio ORM Eloquent son independientes de la base de datos que se use.

Este no es el mecanismo más versátil, ni tampoco el más productivo. De hecho, en los anteriores artículos del Manual de Laravel 5.1, pudimos comprobar que, casi sin código en los modelos éramos capaces de realizar muchas de las operaciones típicas con los datos de las tablas. Por ese motivo, en la mayoría de los casos resultará más interesante usar Eloquent que escribir nuestras propias sentencias SQL.

El motivo de la existencia de Raw SQL es que hay ocasiones en las que las operaciones con las bases de datos son complejas y se requiere la escritura de consultas muy apuradas, que hagan cosas que no puedas a través de otros mecanismos, o bien porque deseas que se ejecuten más rápido. En ese caso podemos bajar hasta el nivel de la base de datos y ejecutar las consultas directamente contra el sistema gestor.

Nota: Realmente con PHP podríamos acceder a los datos a niveles más cercanos a la base de datos, usando extensiones "nativas" para la base de datos en concreto que se desee, como por ejemplo las de MySQL: MySQLi. Sin embargo, llegar a ese nivel es innecesario, porque con las Raw SQL tenemos prácticamente el mismo nivel de control. Sería también contraproducente, porque no podríamos usar nada del código que ya incorpora Laravel para el acceso a datos, pero si aún así lo deseamos, lo podríamos hacer ya que Laravel es PHP al final de cuentas.

Facade DB

La fachada DB nos sirve para ejecutar las consultas crudas. Tiene diversos métodos que debemos invocar en función de la operación dentro de la base de datos: select(), insert(), update(), delete(). Además hay un método statement() para operaciones con el sistema gestor que no devuelven datos, como un "drop table".

El motivo de la existencia de estos métodos es que Laravel te ayuda un poco más al devolverte diversas cosas como resultado al ejecutar las consultas. Dicho en otras palabras, va un poco más allá que ejecutar una sentencia SQL, tal cual, con harías con mysqli_query() por ejemplo.

  • select() siempre devuelve un array de resultados
  • insert() devuelve un boleano indicando si se insertó o no
  • update() devuelve el número de elementos actualizados
  • delete() devuelve el número de elementos borrados

Ahora veamos unos simples pedazos de código que nos servirán como ejemplo.

Selección (select): Comenzaremos por este, en el que recibimos todos los libros de la tabla "books".

$libros = DB::select('select * from books where 1');

Después de la ejecución de esta línea, en $libros encontraremos un array con todos los libros que haya en la tabla.

Una interesante alternativa para selección de elementos nos la ofrece el método selectOne(). La única diferencia es que solo nos selecciona un elemento y por tanto nos evita usar un array. Lo que nos devuelve en su lugar es el objeto del único elemento encontrado (o bien null si no ha encontrado nada).

$libroUnico = DB::selectOne('select * from books where id=1');

Inserción (insert): En el siguiente código insertamos un libro. Además hemos agregado una comprobación para saber si se llegó a insertar y en ese caso devolver un mensaje de respuesta.

$insertado = DB::insert('insert into books (name) values ("La Colmena")');
if($insertado){
     return 'insertado';
}

Actualización (update): En este ejemplo encontrarás una actualización de unos libros y un mensaje indicando cuántos se actualizaron al ejecutarse la consulta.

$num = DB::update('update books set name="Encantos de mujer" where id=55 or id=38');
return "Se han actualizado $num elementos";

Borrado (delete): Y por último una sentencia de borrado, con una línea de código adicional para informar cuántos se han borrado.

$num = DB::update('delete from books where id=65');
return "Se han borrado $num elementos";

"Bindeo" de parametros para filtrado

Una de las preocupaciones más importantes de cualquier desarrollador debería ser la seguridad y en el trabajo con bases de datos lo más crucial es evitar los ataques por inyección de SQL. Muchos de los mecanismos de Laravel para acceso a datos ya tienen implícitos sistemas de filtrado de la información, de modo que no tengas que preocuparte por filtrar tú mismo las variables antes de usarlas en consultas SQL. En el caso de las consultas con Raw SQL, la protección pasa por usar el "bindeo" de parametros en las consultas.

Si no bindeas los parámetros tu código puede ser vulnerable y es un error en el que resutaría fácil de caer cuando se realizan consultas con Raw SQL. Para entender cómo podemos poner en riesgo una aplicación web mediante inyección SQL podemos ver dos códigos que funcionarían aparentemente igual.

1) Código vulnerable:

route::get('rawsql/libros/buscarid', function(){
	$id = \Request::input('id');
	$libros = DB::select('select * from books where id=' . $id);
	dd($libros);
});

El código anterior es vulnerable, porque estamos concatenando directamente en el SQL un valor, sin hacer el bindeo del parámetro. Especialmente, si el valor viene de una entrada de datos por parte del usuario, como es el caso, podría contener caracteres que produzcan SQLs diferentes de las que en principio estamos suponiendo que vamos a enviar.

Nota: No es el objetivo explicar cómo burlar la seguridad de ese código con una inyección de SQL, pero si te interesa puedes buscar documentación en Internet, que hay bastante.

Este segundo código hace exáctamente lo mismo. Pero en este caso sí se realiza el binding del parámetro usando el procedimiento que te asegura la protección de tu sentencia.

2) Código seguro frente a SQL injection:

route::get('rawsql/libros/buscarid', function(){
	$id = \Request::input('id');
	$libros = DB::select('select * from books where id= ?', [$id]);
	dd($libros);
});

Nota: El bindeo de los parámetros no es algo específico de Laravel. Si usas MySQLi por ejemplo también puedes hacer bindeo de parámetros preparando las consultas. O bien con PDO. Quiere decir que las protecciones para el paso de parámetros como datos en las consultas son propias de PHP nativo y que las debes usar aunque no trabajes con ningún framework. Por ese motivo imaginamos que conoces un poco este asunto y no necesitamos explicar mucho más.

Puedes bindear varios parámetros en una consulta, simplemente agregando más variables al array, que harán corresponderse con los caracteres "?" por orden de aparición.

$titulo = "El Quijote";
$autor = "Cervantes";
$insertado = DB::insert('insert into books (name, author) values (?, ?)', [$titulo, $autor]);

Así mismo, puedes realizar el bindeo de datos a la consulta usando parámetros con nombres, en cuyo caso usarás un array asociativo para indicar los valores.

$libros = DB::select('select * from books where name like :busqueda limit :cuantos', array(
'busqueda' => "%$cadena%",
'cuantos' => $num
));

Nota: Un error común en el bindeo de parametros es colocar el binding entre comillas. Por ejemplo "select * from books where name = '?' limit 1". Esto está mal escrito, aunque name tenga valores que son alfanuméricos, puesto que el sistema de incorporación de los parámetros debe ser el encargado de saber si algo va entre comillas o no, y componer la sentencia correctamente. Nunca entrecomillamos los bindeos. Tampoco estaría bien "select * from books where name like '%:busqueda%' limit 3", por el mismo motivo.

Conclusión

Hemos hecho un rápido repaso a el acceso a base de datos a través de la escritura de nuestras propias sentencias SQL. Laravel es capaz de ayudarnos bastante para recuperar la información de las tablas, actualizar, insertar o borrar, entre otras acciones, pero el grado de automatización del acceso a datos no llega a compararse al que tienes mediante otros procedimientos.

Así mismo, el procedimiento que tendrás que realizar en algunos casos puede ser distinto dependiendo del sistema gestor, ya que muchas veces se encuentran cambios en las SQL de uno y otro motor de bases de datos. Sin embargo, si usamos mecanismos de más alto nivel para acceder a BBDD (como Query Builder o el ORM Eloquent), nos aseguramos que el código sea el mismo, independientemente del gestor de bases de datos que estemos usando. Esto creo que sería interesante verlo con un ejemplo para que nos demos cuenta.

Por ejemplo vamos a recuperar el id de la última inserción, con Raw SQL para el motor de bases de datos MySQL.

$titulo = "El Padrino";
$autor = "Mario Puzo";
$insertado = DB::insert('insert into books (name, author) values (?, ?)', [$titulo, $autor]);
if($insertado){
    $id = DB::selectOne('SELECT LAST_INSERT_ID() as "id"');
    return 'Insertado correctamente con id ' . $id->id;
}

No es lo más fácil, ni tampoco lo más legible, pero el problema principal de este código para obtener el identificador del insert realizado es que sólo funcionará para MySQL o MaríaDB. Si queremos cambiar el sistema gestor deberíamos revisar esas líneas de código, lo que es poco atractivo.

No obstante, queremos remarcar que este tampoco es un problema serio en Laravel, puesto como decimos existen otros mecanismos de acceso a las bases de datos que sí permiten crear un código independiente del sistema de base de datos que estemos usando en un momento dado. Continuaremos viendo Query Builder para ir avanzando en nuestro conocimiento y aprender a manejar otros sistemas de Laravel más versátiles que las Raw SQL.

Autor

Miguel Angel Alvarez

Miguel es fundador de DesarrolloWeb.com y la plataforma de formación online EscuelaIT. Comenzó en el mundo del desarrollo web en el año 1997, transformando su hobby en su trabajo.

Compartir

Comentarios

diego_dm2

15/1/2016
Problema usando Raw SQL
Hola. Primero felicitarte por el artículo. Yo ya he utilizado alguna vez lo que comentas, pero he tenido problemas ya que Laravel cuando utilizas raw sql, en lugar de devolver un modelo de eloquent, te devuelve un stdObject, por lo que pierdes los métodos que tengas asociados al modelo (yo venía de otro framework, en el que se devolvía un modelo siempre). Yo cuando he tenido que hacer consultas complejas, lo que he hecho ha sido hacer una sql y luego los resultados obtenidos, volcarlos al modelo que me interesaba, usando el método hydrate, pero no se si hay algún mecanismo más óptimo el Laravel para hacer consultas complejas y obtener modelos.

micromante

18/1/2016
hydrateRaw
Buenas tardes diego_dm2,
Efectivamente, en Laravel puedes usar los metodos de HydrateRaw para hacer un cast de una SQL a bajo nivel sobre un objeto Eloquent. En la documentacion oficial, en el listado de metodos de un modelo puedes encontrar como hacer esta sencilla operacion. https://laravel.com/api/5.0/Illuminate/Database/Eloquent/Model.html#method_hydrateRaw

Espero te sirva, cualquier cosa te dejo mi twitter @micromante,

Saludos!

rogelio

22/1/2016
sql
hola mi nombre es rogelio
mi principal duda es con sql el anyo pasado estaba programando en una institucion en la cuan me pedian directamente sql para mi me resulto complicado ya que la sentencias sql en particular para mi es algo complicado, me podrias facilitar o recomendar un curso o un libro en el cual pueda estudiar este lenguaje
de antemano gracias y saludos