Tablas de bases de datos personalizadas: la seguridad es lo primero

Spanish (Español) translation by Steven (you can also view the original English article)

Esta es la segunda parte de una serie sobre tablas de bases de datos personalizadas en WordPress. En la primera parte, cubrimos las razones a favor y en contra del uso de tablas personalizadas. Analizamos algunos de los detalles que deberían tenerse en cuenta (nombre de columna, tipos de columna) y cómo crear la tabla. Antes de continuar, debemos explicar cómo interactuar con esta nueva tabla de forma segura. En un artículo anterior cubrí el saneamiento general y la validación; en este tutorial, veremos esto con más detalle en el contexto de las bases de datos.

La seguridad al interactuar con una tabla de base de datos es primordial, por eso lo cubriremos al principio de la serie. Si no se hace correctamente, puedes dejar tu tabla abierta a la manipulación mediante inyección SQL. Esto podría permitir que un hacker extraiga información, reemplace contenido o incluso altere la forma en que se comporta tu sitio, y el daño que podría causar no se limita a tu tabla personalizada.

Supongamos que queremos permitir que los administradores eliminen registros de nuestro registro de actividad. Un error común que he visto es el siguiente:


     if ( !empty($_GET['action'])
          && 'delete-activity-log' == $_GET['action'] 
          && isset($_GET['log_id']) ) {
               
               global $wpdb;
               unsafe_delete_log($_GET['log_id']);
          
     }
 
     function unsafe_delete_log( $log_id ){
          global $wpdb;
          $sql = "DELETE FROM {$wpdb->wptuts_activity_log} WHERE log_id = $log_id";
          $deleted = $wpdb->query( $sql );
     }

Entonces, ¿qué pasa aquí? Muchos: no han verificado los permisos, por lo que cualquiera puede eliminar un registro de actividad. Tampoco han verificado los 'nonces', por lo que incluso con verificaciones de permisos, un usuario administrador podría ser engañado para que elimine un registro. Todo esto se cubrió en este tutorial. Pero su tercer error agrava los dos primeros: la función unsafe_delete_log() usa el valor pasado en un comando SQL sin escapar primero. Esto lo deja muy abierto a la manipulación.

Supongamos que su uso previsto es:

1
2	www.unsafe-site.com?action=delete-activity-log&log_id=7

¿Qué pasa si un atacante visita (o engaña a un administrador para que visite): www.unsafe-site.com?action=delete-activity-log&log_id=1;%20DROP%20TABLE%20wp_posts. El log_id contiene un comando SQL, que posteriormente se inyecta en $sql y se ejecutaría como:

1
2	DELETE from wp_wptuts_activity_log WHERE log_id=1; DROP TABLE wp_posts

El resultado: se elimina toda la tabla wp_posts. He visto código como este en foros y el resultado es que cualquiera que visite tu sitio puede actualizar o eliminar cualquier tabla de tu base de datos.

Si se corrigieran los dos primeros errores, sería más difícil que este tipo de ataque funcione, pero no imposible, y no protegería contra un "atacante" que tenga permiso para eliminar registros de actividad. Es increíblemente importante proteger tu sitio contra las inyecciones de SQL. También es increíblemente simple: WordPress proporciona el método llamado prepare. En este ejemplo particular:


     function safe_delete_log( $log_id ){
         global $wpdb;
         $sql = $wpdb->prepare("DELETE from {$wpdb->wptuts_activity_log} WHERE log_id = %d", $log_id);
         $deleted = $wpdb->query( $sql )
     }

El comando SQL ahora se ejecutaría como:

1
2	DELETE from wp_wptuts_activity_log WHERE log_id=1;

Sanitizando las consultas de la base de datos

La mayor parte de la sanitización se puede realizar únicamente con el global $wpdb, especialmente a través de su método prepare. También proporciona métodos para insertar y actualizar datos en tablas de forma segura. Estos suelen funcionar reemplazando una entrada desconocida o asociando una entrada con un marcador de posición de formato. Este formato le dice a WordPress qué datos debe esperar:

%s denota una cadena de caracteres.
%d denota un entero.
%f denota un número flotante.

Comenzamos analizando tres métodos que no solo sanitizan las consultas, sino que también las crean para ti.

Inserción de datos

WordPress proporciona el método $wpdb->insert(). Es un contenedor para insertar datos en la base de datos y se encarga de la sanitización. Toma tres parámetros:

Nombre de la tabla: el nombre de la tabla.
Datos: matriz de datos para insertar como columna->pares de valores.
Formatos: matriz de formatos para el valor correspondiente en la matriz de datos (por ejemplo,%s,%d,%f).

Ten en cuenta que las claves de los datos deben ser columnas: si hay una clave que no coincide con una columna, se puede producir un error.

En los ejemplos que siguen, hemos establecido explícitamente los datos, pero por supuesto, en general, estos datos provienen de la entrada del usuario, por lo que podría ser cualquier cosa. Como se discutió en este artículo, los datos deberían haberse validado primero, para devolver cualquier error al usuario, pero aún necesitamos desinfectar los datos antes de agregarlos a nuestra tabla. Veremos la validación en el próximo artículo de esta serie.


global $wpdb;
// 
$user_id = 1;
$activity = 1;
$object_id = 1479;
$activity_date = date_i18n('Y-m-d H:i:s', false, true);
$inserted = $wpdb->insert(
     $wpdb->wptuts_activity_log,
     array(
        'user_id'=>$user_id,
        'activity'=>$activity,
        'object_id'=>$object_id,
        'activity_date'=> $activity_date,
      ),
     array (
        '%d',
        '%s',
        '%d',
        '%s',
     )
 );
 if( $inserted ){
    $insert_id = $wpdb->insert_id;
 }else{
    //Insert failed
 }

Actualización de datos

Para actualizar los datos en la base de datos tenemos $wpdb->update(). Este método acepta cinco argumentos:

Nombre de la tabla: el nombre de la tabla
Datos: matriz de datos para insertar como columna->pares de valores.
Where: matriz de datos para que coincida como columna->pares de valores.
Formato de datos: matriz de formatos para los valores de "datos" correspondientes.
Formato del 'Where': matriz de formatos para los valores "where" correspondientes.

Esto actualiza las filas que coinciden con la matriz 'where' con los valores de la matriz de datos. Nuevamente, como con $wpdb->insert(), las claves de la matriz de datos deben coincidir con una columna. Devuelve false en caso de error o el número de filas actualizadas.

En el siguiente ejemplo, actualizamos cualquier registro con el ID de registro '14' (que debería ser como máximo un registro, ya que esta es nuestra clave principal). Actualiza el ID de usuario a 2 y la actividad a 'editado'.


global $wpdb;
$user_id=2;
$activity='edited';
$log_id = 14;
$updated = $wpdb->update(
     $wpdb->wptuts_activity_log,
     array(
        'user_id'=>$user_id, 
        'activity'=>$activity,
     ),
     array('log_id'=>$log_id,),
     array( '%d', '%s'),
     array( '%d'),
 );
 if( $updated ){
    //Number of rows updated = $updated
 }

Eliminar

Desde WordPress 3.4 también se ha proporcionado el método $wpdb->delete() para eliminar filas de manera fácil (y segura). Este método toma tres parámetros:

Nombre de la tabla: el nombre de la tabla.
Where: matriz de datos para que coincida como columna->pares de valores.
Formatos: matriz de formatos para el tipo de valor correspondiente (por ejemplo,%s,%d,%f)

Si deseas que tu código sea compatible con WordPress en una versión anterior a 3.4, deberás usar el método $wpdb->prepare para sanitizar la instrucción SQL apropiada. Un ejemplo de esto se dio arriba. El método $wpdb->delete devuelve el número de filas eliminadas, o falso en caso contrario, para que puedas determinar si la eliminación se realizó correctamente.


global $wpdb;
$deleted = $wpdb->delete(
     $wpdb->wptuts_activity_log,
     array('log_id'=>14,),
     array( '%d'),
 );
 if( $deleted ){
    //Number of rows deleted = $deleted
 }

esc_sql

A la luz de los métodos anteriores, y del método $wpdb->prepare() más general que se analiza a continuación, esta función es un poco redundante. Se proporciona como un contenedor útil para el método $wpdb->escape(), que en sí mismo es un agregado de barras glorificado llamado addslashes. Como suele ser más apropiado y recomendable utilizar los tres métodos anteriores, o $wpdb->prepare(), probablemente encontrarás que rara vez necesitas utilizar esc_sql().

Como un simple ejemplo:

1
2	$activity = 'commented';
3	$sql = "DELETE FROM {$wpdb->wptuts_activity_log} WHERE activity='".esc_sql($activity)."';";

Consultas generales

Para comandos SQL generales donde (es decir, aquellos que no insertan, eliminan o actualizan filas) tenemos que usar el método $wpdb->prepare(). Acepta un número variable de argumentos. La primera es la consulta SQL que deseamos ejecutar con todos los datos "desconocidos" reemplazados por su marcador de posición de formato apropiado. Estos valores se pasan como argumentos adicionales, en el orden en que aparecen.

Por ejemplo en lugar de:


$sql = "SELECT* FROM {$wpdb->wptuts_activity_log} 
         WHERE user_id = $user_id 
         AND object_id = $object_id 
         AND activity = $activity 
         ORDER BY activity_date $order";
$logs = $wpdb->get_results($sql);

tenemos


$sql = $wpdb->prepare("SELECT* FROM {$wpdb->wptuts_activity_log} 
                WHERE user_id = %d 
                AND object_id = %d 
                AND activity = %s 
                ORDER BY activity_date %s", 
               $user_id,$object_id,$activity, $order  );
$logs = $wpdb->get_results($sql);

El método prepare hace dos cosas.

Aplica mysql_real_escape_string() (o addedlashes()) a los valores que se insertan. En particular, esto evitará que los valores que contienen comillas salten de la consulta.
Esto aplica vsprintf() al agregar los valores a la consulta para asegurarse de que tengan el formato apropiado (por lo que los enteros son enteros, los flotantes son flotantes, etc.). Es por eso que nuestro ejemplo al principio del artículo eliminó todo menos el '1'.

Consultas más complicadas

Deberías encontrar que $wpdb->prepare, junto con los métodos de inserción, actualización y eliminación son todo lo que realmente necesitas. A veces, aunque hay circunstancias en las que se desea un enfoque más "manual", a veces solo desde el punto de vista de la legibilidad. Por ejemplo, supongamos que tenemos una matriz desconocida de actividades para las que queremos todos los registros. *podríamos* agregar dinámicamente los marcadores de posición %s a la consulta SQL, pero un enfoque más directo parece más fácil:


 //An unknown array that should contain strings being queried for
 $activities = array( ... ); 

 //Sanitize the contents of the array
 $activities = array_map('esc_sql',$activities);
 $activities = array_map('sanitize_title_for_query',$activities);

 //Create a string from the sanitised array forming the inner part of the IN( ... ) statement
 $in_sql = "'". implode( "','", $activities ) . "'";

 //Add this to the query
 $sql = "SELECT* FROM $wpdb->wptuts_activity_log WHERE activity IN({$in_sql});"

 //Perform the query
 $logs = $wpdb->get_results($sql);

La idea es aplicar esc_sql y sanitize_title_for_query a cada elemento de la matriz. El primero agrega barras para escapar de los términos, similar a lo que hace $wpdb->prepare(). El segundo simplemente aplica sanitize_title_with_dashes(), aunque el comportamiento se puede modificar por completo a través de filtros. La declaración SQL real se forma implosionando la matriz ahora sanitizada en una cadena separada por comas, que se agrega a la parte IN(...) de la consulta.

Si se espera que la matriz contenga números enteros, basta con usar intval() o absint() para desinfectar cada elemento de la matriz.

Listas blancas

En otros casos, la inclusión en listas blancas puede ser apropiada. Por ejemplo, la entrada desconocida puede ser una matriz de columnas que se devolverán en la consulta. Como sabemos cuáles son las columnas de la base de datos, podemos simplemente incluirlas en la lista blanca, eliminando cualquier campo que no reconozcamos. Sin embargo, para que nuestro código sea amigable para los humanos, no debemos distinguir entre mayúsculas y minúsculas. Para hacer esto, convertiremos todo lo que recibamos a minúsculas, ya que en la parte uno usamos específicamente nombres de columna en minúsculas.


 //An unknown array that should contain columns to be included in the query
 $fields = array( ... ); 

 //A whitelist of allowed fields
 $allowed_fields = array( ... );    

 //Convert fields to lowercase (as our column names are all lower case - see part 1)
 $fields = array_map('strtolower',$fields);

 //Sanitize by white listing
 $fields = array_intersect($fields, $allowed_fields);

 //Return only selected fields. Empty $fields is interpreted as all
 if( empty($fields) ){
     $sql = "SELECT* FROM {$wpdb->wptuts_activity_log}";
 }else{
     $sql = "SELECT ".implode(',',$fields)." FROM {$wpdb->wptuts_activity_log}";
 }

 //Perform the query   
 $logs = $wpdb->get_results($sql);

La lista blanca también es conveniente cuando se configura la parte ORDER BY de la consulta (si esto lo establece la entrada del usuario): los datos se pueden ordenar como DESC o ASC solamente.


     //Unknown user input (expected to be asc or desc)
     $order = $_GET['order']; 

     //Allow input to be any, or mixed, case
     $order = strtoupper($order);
     
     //Sanitised order value
     $order = ( 'ASC' == $order ? 'ASC' : 'DEC' );

Consultas de tipo LIKE

Las sentencias SQL LIKE admiten el uso de comodines como % (cero o más caracteres) y _ (exactamente un carácter) al hacer coincidir valores con la consulta. Por ejemplo, el valor foobar coincidiría con cualquiera de las consultas:


 SELECT * FROM $wpdb->wptuts_activity_log WHERE activity LIKE 'foo%'
 SELECT * FROM $wpdb->wptuts_activity_log WHERE activity LIKE '%bar'
 SELECT * FROM $wpdb->wptuts_activity_log WHERE activity LIKE '%oba%'
 SELECT * FROM $wpdb->wptuts_activity_log WHERE activity LIKE 'fo_bar%'

Sin embargo, estos caracteres especiales pueden estar presentes en el término que se busca, por lo que para evitar que se interpreten como comodines, debemos escapar de ellos. Para esto, WordPress proporciona la función like_escape(). Ten en cuenta que esto no evita la inyección de SQL, sino que solo escapa los caracteres % y _: aún necesitas usar esc_sql() o $wpdb->prepare().


 //Collect term
 $term = $_GET['activity'];

 //Escape any wildcards
 $term = like_escape($term);

 $sql = $wpdb->prepare("SELECT* FROM $wpdb->wptuts_activity_log WHERE activity LIKE %s", '%'.$term.'%');

 $logs = $wpdb->get_results($sql);

Funciones del contenedor de consultas

En los ejemplos que hemos visto, usamos otros dos métodos de $wpdb:

$wpdb->query( $sql ): realiza cualquier consulta que se le dé y devuelve el número de filas afectadas.
$wpdb->get_results( $sql, $ouput): esto realiza la consulta que se le ha asignado y devuelve el conjunto de resultados coincidentes (es decir, las filas coincidentes). $output establece el formato de los resultados devueltos:
- ARRAY_A: matriz numérica de filas, donde cada fila es una matriz asociativa, codificada por las columnas.
- ARRAY_N: matriz numérica de filas, donde cada fila es una matriz numérica.
- OBJECT: matriz numérica de filas, donde cada fila es un objeto de fila. Por defecto.
- OBJECT_K: matriz asociativa de filas (codificada por el valor de la primera columna), donde cada fila es una matriz asociativa.

Hay otros que tampoco hemos mencionado:

$wpdb->get_row( $sql, $ouput, $row): esto realiza la consulta y devuelve una fila. $row establece qué fila se devolverá, por defecto es 0, la primera fila coincidente. $output establece el formato de la fila:
- ARRAY_A: la fila es un par de column=>value.
- ARRAY_N: la fila es una matriz numérica de valores.
- OBJECT: la fila se devuelve como un objeto. Por defecto.
$wpdb->get_col( $sql, $column): esto realiza la consulta y devuelve una matriz numérica de valores de la columna especificada. $column especifica qué columna devolver como entero. De forma predeterminada, es 0, la primera columna.
$wpdb->get_var( $sql, $column, $row): esto realiza la consulta y devuelve un valor particular. $row y $column son como arriba, y especifican qué valor devolver. Por ejemplo,
1
2

$activities_by_user_1 = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->wptuts_activity_log} WHERE user_id = 1");

Es importante tener en cuenta que estos métodos son solo envoltorios para realizar una consulta SQL y formatear el resultado. No sanitizan la consulta, por lo que no debes usarlos solos cuando la consulta contiene algunos datos "desconocidos".

Resumen

Hemos cubierto bastante en este tutorial, y el saneamiento de datos es un tema importante de entender. En el próximo artículo lo aplicaremos a nuestro complemento. Buscaremos desarrollar un conjunto de funciones contenedoras (similares a funciones como wp_insert_post(), wp_delete_post(), etc.) que agregarán una capa de abstracción entre nuestro complemento y la base de datos.

1
2	$activities_by_user_1 = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->wptuts_activity_log} WHERE user_id = 1");