Пользовательские таблицы базы данных: безопасность прежде всего

Russian (Pусский) translation by Liliya (you can also view the original English article)

Это вторая часть серии о пользовательских таблицах базы данных в WordPress. В первой части мы рассмотрели все за и против использования пользовательских таблиц. Мы рассмотрели некоторые детали, которые необходимо учитывать, - именование столбцов, типы столбцов - а также способы создания таблицы. Прежде чем идти дальше, мы должны обсудить, как безопасно взаимодействовать с этой новой таблицей. В предыдущей статье я рассмотрел общие вопросы санитарии и проверки - в этом руководстве мы рассмотрим это более подробно в контексте баз данных.

Безопасность при взаимодействии с таблицей базы данных имеет первостепенное значение, поэтому мы рассмотрим ее в начале этой серии. Если все сделано неправильно, вы можете оставить свою таблицу открытой для манипуляций с помощью SQL-инъекции. Это может позволить хакеру извлечь информацию, заменить контент или даже изменить поведение вашего сайта - и ущерб, который они могут нанести, не ограничивается вашей пользовательской таблицей.

Предположим, мы хотим разрешить администраторам удалять записи из нашего журнала активности. Распространенная ошибка, которую я видел, заключается в следующем:

     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 );
     }

Так что здесь не так? Много чего: они не проверяли разрешения, поэтому любой может удалить журнал активности. Они также не проверяли одноразовые номера, поэтому даже при проверке разрешений пользователь-администратор может быть обманут в удалении журнала. Все это было описано в этом уроке. Но их третья ошибка объединяет первые две: функция unsafe_delete_log() использует переданное значение в команде SQL, не экранируя его первым. Это оставляет его широко открытым для манипуляций.

Давайте предположим, что его предполагаемое использование это

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

Что делать, если злоумышленник посетил (или обманул администратора): www.unsafe-site.com?action=delete-activity-log&log_id=1;%20DROP%20TABLE%20wp_posts. Log_id содержит команду SQL, которая впоследствии вводится в $sql и будет выполняться как:

    DELETE from wp_wptuts_activity_log WHERE log_id=1; DROP TABLE wp_posts

Результат: вся таблица wp_posts удалена. Я видел такой код на форумах, и в результате любой посетитель их сайта может обновить или удалить любую таблицу в своей базе данных.

Если первые две ошибки были исправлены, то это затруднит работу этого типа атак - но возможно, и это не защитит от «злоумышленника», у которого есть разрешение на удаление журналов активности. Невероятно важно защитить свой сайт от SQL-инъекций. Это также невероятно просто: WordPress предоставляет метод prepare. В этом конкретном примере:

     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 )
     }

Команда SQL теперь будет выполняться как

    DELETE from wp_wptuts_activity_log WHERE log_id=1;

Санитарная обработка запросов к базе данных

Большую часть санации можно выполнить исключительно с использованием $wpdb global, особенно с помощью метода prepare. Он также предоставляет методы для вставки и обновления данных в таблицы безопасно. Обычно они работают путем замены неизвестного ввода или связывания ввода с заполнителем формата. Этот формат сообщает WordPress, какие данные ожидать:

%s обозначает строку
%d обозначает целое число
%f обозначает float

Мы начнем с рассмотрения трех методов, которые не только очищают запросы, но и создают их для вас.

Вставка данных

WordPress предоставляет метод $wpdb-> insert(). Это обложка для вставки данных в базу данных и обработки санации. Требуется три параметра:

Table name - название таблицы
Data - массив данных для вставки в виде пар столбец-> значение
Formats - массив форматов для соответствующего значения в массиве данных (например,%s,%d,%f)

Обратите внимание, что ключи данных должны быть столбцами: если есть ключ, который не соответствует столбцу, может быть выдана ошибка.

В следующих примерах мы явно установили данные - но, конечно, в целом, эти данные были бы получены из пользовательского ввода - так что это может быть что угодно. Как обсуждалось в этой статье, данные должны были быть проверены в первую очередь, чтобы возвратить любые ошибки пользователю - но нам все еще нужно очистить данные перед добавлением их в нашу таблицу. Мы будем смотреть на проверку в следующей статье этой серии.

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
 }

Обновление данных

Для обновления данных в базе данных у нас есть $wpdb-> update(). Этот метод принимает пять аргументов:

Table name - название таблицы
Data - массив данных для обновления в виде пар столбец-> значение
Where - массив данных для сопоставления в виде столбца -> пары значений
Data Format - массив форматов для соответствующих значений данных
Where Format - массив форматов для соответствующих значений «where»

Это обновляет все строки, которые соответствуют массиву where, значениями из массива данных. Как и в случае $wpdb->insert(), ключи массива данных должны соответствовать столбцу. Возвращает false в случае ошибки или количества обновленных строк.

В следующем примере мы обновляем любые записи с идентификатором журнала '14'(который должен быть не более одной записи, так как это наш первичный ключ). Обновляет идентификатор пользователя до 2, а активность - до «отредактировано».

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
 }

Удаление

Начиная с 3.4, WordPress также предоставляет метод $wpdb->delete() для простого (и безопасного) удаления строк. Этот метод принимает три параметра:

Table name - название таблицы
Where - массив данных для сопоставления в виде столбца -u003e пары значений
Formats - массив форматов для соответствующего типа значения (например,%s,%d,%f)

Если вы хотите, чтобы ваш код был совместим с WordPress pre-3.4, вам нужно будет использовать метод $wpdb->prepare для очистки соответствующего оператора SQL. Пример этого был приведен выше. Метод $wpdb->delete возвращает количество удаленных строк, в противном случае - false, чтобы вы могли определить, было ли удаление успешным.

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

esc_sql

В свете описанных выше методов и более общего метода $wpdb->prepare(), обсуждаемого далее, эта функция немного избыточна. Он предоставляется как полезная оболочка для метода $wpdb->escape(), который сам по себе является прославленной addslashes. Поскольку обычно более целесообразно и целесообразно использовать вышеупомянутые три метода или $wpdb->prepare(), вы, вероятно, обнаружите, что вам редко требуется использовать esc_sql().

В качестве простого примера:

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

Общие запросы

Для общих команд SQL, где (то есть те, которые не вставляют, удаляют или обновляют строки), мы должны использовать метод $wpdb->prepare(). Он принимает переменное количество аргументов. Первый - это SQL-запрос, который мы хотим выполнить со всеми «неизвестными» данными, замененными заполнителями соответствующего формата. Эти значения передаются как дополнительные аргументы в порядке их появления.

Например, вместо:

$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);

у нас есть

$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);

Метод prepare делает две вещи.

Он применяет mysql_real_escape_string() (или addlashes()) к вставляемым значениям. В частности, это предотвратит выпадение значений, содержащих кавычки, из запроса.
Он применяет vsprintf() при добавлении значений в запрос, чтобы убедиться, что они соответствующим образом отформатированы (целые числа являются целыми числами, числами с плавающей точкой и т.д.) Вот почему наш пример в самом начале статьи вычеркнул все, кроме «1».

Более сложные запросы

Вы должны обнаружить, что $wpdb->prepare, а также методы вставки, обновления и удаления - все, что вам действительно нужно. Иногда, хотя бывают обстоятельства, когда требуется более «ручной» подход, иногда просто с точки зрения читабельности. Например, предположим, у нас есть неизвестный массив действий, для которых мы хотим все журналы. Мы *могли бы* динамически добавить заполнители %s в SQL-запрос, но более прямой подход кажется более простым:

 //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);

Идея состоит в том, чтобы применить esc_sql и sanitize_title_for_query к каждому элементу в массиве. Первый добавляет косую черту, чтобы избежать терминов - аналогично тому, что делает $wpdb->prepare(). Второй просто применяет sanitize_title_with_dashes() - хотя поведение может быть полностью изменено с помощью фильтров. Фактический оператор SQL формируется путем вставки очищенного массива в строку, разделенную запятыми, которая добавляется в часть IN(...).

Если ожидается, что массив будет содержать целые числа, тогда достаточно использовать intval() или absint() для очистки каждого элемента в массиве.

Whitelisting

В других случаях белый список может быть уместным. Например, неизвестный ввод может быть массивом столбцов, которые должны быть возвращены в запросе. Поскольку мы знаем, что такое столбцы базы данных, мы можем просто внести их в белый список - удалив любые поля, которые мы не распознаем. Однако, чтобы сделать наш код понятным для человека, мы должны учитывать регистр символов. Для этого мы преобразуем все, что мы получаем, в нижний регистр - поскольку в первой части мы специально использовали имена столбцов в нижнем регистре.

 //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);

Белый список также удобен при установке части запроса ORDER BY (если это установлено пользовательским вводом): данные можно упорядочить только как DESC или ASC.

     //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' );

Как запросы

Операторы SQL LIKE поддерживают использование подстановочных знаков, таких как % (ноль или более символов) и _ (ровно один символ), при сопоставлении значений в запросе Например, значение foobar будет соответствовать любому из запросов:

 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%'

Однако эти специальные символы могут фактически присутствовать в искомом термине - и, таким образом, чтобы их нельзя было интерпретировать как символы подстановки - нам нужно избегать их. Для этого WordPress предоставляет функцию like_escape(). Обратите внимание, что это не предотвращает внедрение SQL-кода, а только экранирует символы % и _: вам все еще нужно использовать esc_sql() или $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);

Query Wrapper Функции

В примерах, которые мы посмотрели, мы использовали два других метода $wpdb:

$wpdb->query( $sql ) - выполняет любой заданный ему запрос и возвращает количество затронутых строк.
$wpdb->get_results( $sql, $ouput) - выполняет заданный ему запрос и возвращает соответствующий набор результатов (т.е. соответствующие строки) $output устанавливает формат возвращаемых результатов:
- ARRAY_A - числовой массив строк, где каждая строка представляет собой ассоциативный массив, снабженный столбцами.
- ARRAY_N - числовой массив строк, где каждая строка является числовым массивом.
- OBJECT - числовой массив строк, где каждая строка является объектом строки. По умолчанию.
- OBJECT_K - ассоциативный массив строк (определяется значением первого столбца), где каждая строка является ассоциативным массивом.

Есть и другие, о которых мы тоже не упомянули:

$wpdb->get_row( $sql, $ouput, $row) - выполняет запрос и возвращает одну строку. $row устанавливает, какая строка должна быть возвращена, по умолчанию это 0, первая подходящая строка. $output устанавливает формат строки:
- ARRAY_A - строка представляет собой column=>value.
- ARRAY_N - строка представляет собой числовой массив значений.
- OBJECT - строка возвращается как объект. По умолчанию.
$wpdb->get_col( $sql, $column) - выполняет запрос и возвращает числовой массив значений из указанного столбца. $column указывает, какой столбец нужно вернуть как целое число. По умолчанию это 0, первый столбец.
$wpdb->get_var( $sql, $column, $row) - выполняет запрос и возвращает определенное значение. $row и $column такие же, как указано выше, и указывают, какое значение вернуть. Например,
```
$activities_by_user_1 = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->wptuts_activity_log} WHERE user_id = 1");
```

Важно отметить, что эти методы являются просто оболочками для выполнения запроса SQL и форматирования результата. Они не очищают запрос, поэтому вам не следует использовать их в одиночку, когда запрос содержит некоторые «неизвестные» данные.

Подведем итоги

В этом уроке мы довольно много рассмотрели - и очистка данных - важная тема для понимания. В следующей статье мы применим его к нашему плагину. Мы рассмотрим разработку набора функций-оболочек (похожих на такие функции, как wp_insert_post(), wp_delete_post() и т. д.), Которые добавят слой абстракции между нашим плагином и базой данных.