Руководство для начинающих разработчиков по использованию собственных SQL запросов в WordPress

Прежде всего хочется отметить одну вещь. За все время моей карьеры веб-разработчика было не так уж и много случаев, когда приходилось получать что-то из базы данных WordPress прямыми SQL запросами. Чаще всего встроенные в ядро механизмы справлялись с задачей. Методы  WP_Query, WP_Term_Query, WP_User_Query позволяют выполнять достаточно сложные запросы без необходимости писать свои команды SQL. Но бывают ситуации, когда ни один из стандартных способов не дает желаемого результата, или делает это совершенно не оптимальным образом. Эта статья — как раз о таких ситуациях.

Бывает, что для получения нужных данных лучше всего написать собственный SQL запрос. Чаще всего он просто быстрее, но бывает что данные вообще по-другому не получить, например если они лежат в произвольных таблицах, созданных с помощью плагина ACF Custom Database Tables.

Информация в этой статье не является исчерпывающей инструкцией по работе с SQL, но даст вам хороший фундамент для понимания принципов работы базы данных и практик для написания собственных запросов. Обратите внимание, что здесь рассматривается получение данных ИЗ базы данных, поэтому если вам нужно записать что-то в базу с помощью произвольных SQL команд, настоятельно советую начать с чтения официальной документации метода wpdb. Там описаны примеры запросов для некоторых задач, таких как вставка новых строк, замена существующих строк, обновление строк, и удаление строк в базе данных WordPress.

Про структуру этой статьи

Мы начнем с краткого обзора базовых SQL команд, которые могут понадобиться вам для получения разных данных из базы. Эта секция будет состоять практически полностью из чистого SQL кода, который не работает в PHP сам по себе.

После этого мы перейдем непосредственно к использованию этих команд в среде WordPress и затем пройдемся по способам защиты SQL. При работе c SQL нельзя переоценить значение безопасности, важно чтобы все команды были защищены от атак типа SQL injection.

Все примеры кода из первой секции работают только в среде MySQL, с помощью командной строки или любого клиента баз данных, таких как TablePlus, Sequel Pro, MySQL Workbench, or phpMyAdmin.

ВНИМАНИЕ! Много боли впереди…

Пожалуйста, никогда не выполняйте команды SQL на боевой базе данных. Вы можете потерять или повредить все свои данные, поэтому повторю еще раз — всегда разрабатывайте и тестируйте на локальном/тестовом окружении.

Базовые команды SQL

Начнем с быстрого обзора некоторых простых SQL команд для выборок данных из произвольных таблиц. Так как эта статья рассчитана на начинающих разработчиков, мы будем рассматривать ситуации с учетом использованием плагина создания собственных таблиц — ACF Custom Database Tables.

Простые выборки с помощью SELECT

Самая простая команда SQL — SELECT. По-сути, это запрос на получение всех данных из выбранной таблицы, например:

SELECT * FROM some_table_name;

sql-in-wordpress-example-01.sql hosted with ❤ by GitHub

Он работает, но возвращает абсолютно все данные, что часто не требуется. Можно уточнить запрос, указав только нужные колонки, вот так:

SELECT name, profession, hourly_rate FROM some_table_name;

sql-in-wordpress-example-02.sql hosted with ❤ by GitHub

Указание нужных колонок сужает запрос и он отдает нам только те данные, которые нужны сейчас. Вместо всех колонок таблицы, MySQL вернет только 3 — name, profession, и hourly_rate. Но мы все равно получаем все строки указанных колонок, что обычно не нужно. Давайте посмотрим, как можно сузить запрос еще дальше с помощью оператора WHERE.

Использование оператора WHERE для запроса данных по условиям

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

SELECT * FROM some_table_name WHERE hourly_rate = 90;

sql-in-wordpress-example-03.sql hosted with ❤ by GitHub

Вы примере выше мы получили только те строки, в которых значение колонки hourly_rate равно 90. Давайте разберем другой пример, в котором условия является строкой:

SELECT * FROM some_table_name WHERE profession = 'architect';

sql-in-wordpress-example-04.sql hosted with ❤ by GitHub

Обратите внимание: при работе со строками нужно оборачивать их значения в кавычки. Теперь давайте представим, что вам нужно найти все строки, в которых значения совпадают с одним из возможных вариантов:

SELECT * FROM some_table_name WHERE profession IN ('architect', 'draftsperson', 'builder');

sql-in-wordpress-example-05.sql hosted with ❤ by GitHub

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

# Найти строки, в которых значение колонки hourly_rate меньше 100.
SELECT * FROM some_table_name WHERE hourly_rate < 100;

# Найти строки, в которых значение колонки hourly_rate меньше или равно 100.
SELECT * FROM some_table_name WHERE hourly_rate <= 100;

# Найти строки, в которых значение колонки hourly_rate больше 50.
SELECT * FROM some_table_name WHERE hourly_rate > 50;

# Найти строки, в которых значение колонки hourly_rate больше или равно 50.
SELECT * FROM some_table_name WHERE hourly_rate >= 50;

# Найти строки, в которых значение колонки hourly_rate НЕ равно 50.
SELECT * FROM some_table_name WHERE hourly_rate <> 50;

sql-in-wordpress-example-06.sql hosted with ❤ by GitHub

Можно пойти еще дальше и выбрать строки со значениями из заданного диапазона, используя оператор BETWEEN.

# Найдем строки со значением hourly rate от 60 до 120.
SELECT * FROM some_table_name WHERE hourly_rate BETWEEN 60 AND 120;

sql-in-wordpress-example-07.sql hosted with ❤ by GitHub

Множественные условия с помощью операторов AND & OR

Полезной функцией SQL является возможность сужать наши WHERE запросы с помощью операторов AND и OR:

# Выберем все строки из таблицы some_table_name со значением колонки profession равным architect И значением колонки hourly_rate менее 100

SELECT * FROM some_table_name WHERE profession = 'architect' AND hourly_rate < 100;

# Выберет все строки из таблицы some_table_name в которых значение колонки profession равно architect ИЛИ hourly_rate больше 100

SELECT * FROM some_table_name WHERE profession = 'architect' OR hourly_rate > 100;

sql-in-wordpress-example-08.sql hosted with ❤ by GitHub

При необходимости, эти ключевые слова можно компоновать и комбинировать с помощью скобок:

# Вывести все строки со значением profession равным builder И hourly_rate ЛИБО меньше 60, ЛИБО больше 80

SELECT * FROM some_table_name WHERE profession = 'builder' AND (hourly_rate < 60 OR hourly_rate > 80);

sql-in-wordpress-example-09.sql hosted with ❤ by GitHub

Отрицательное условие с использованием оператора NOT

Оператор NOT по сути превращает ваше условие в отрицательное. Например:

# Выведет все строки, в которых profession не равно 'architect'.
SELECT * FROM some_table_name WHERE NOT profession = 'architect';

# Покажет все строки, в которых значение profession не равно 'architect' или 'builder';
SELECT * FROM some_table_name WHERE NOT profession = 'architect' AND NOT profession = 'builder';

# Предыдущий запрос можно еще написать вот так
SELECT * FROM some_table_name WHERE NOT profession IN ('architect', 'builder');

sql-in-wordpress-example-10.sql hosted with ❤ by GitHub

Используем LIKE и ‘wildcards’ для паттернов сопоставления

С большой долей вероятности вам будут встречаться ситуации, когда потребуется найти все строки со значением колонки, начинающимся или заканчивающимся определенным фрагментом. Для подобных задач можно использовать оператор LIKE, с которым мы познакомимся чуть позже. Но сначала нужно разобраться с символами % и _. Они называются подстановочные символы (wildcards) и могут означать любое количество символов любого типа.

Подстановочный символ %

% служит для обозначения любого количества произвольных символов: букв, цифр и т.д. Он может заменить один символ, пустоту или неограниченное количество символов. Например, если в запросе есть паттерн draft%, то результат будет содержать все строки, в которых есть значения, начинающиеся на draft:

• draft
• draftsperson
• draftswoman
• draftsman
• draft beer brewer
• draftfkerjsdofgsdfgzsdfgsdfg

Примеры использования подстановочных символов:

# Получить все ряды, в которых значение столбца profession начинается с 'draft';

SELECT * FROM some_table_name WHERE profession LIKE 'draft%';


# Получить все ряды, в которых значение столбца profession заканчивается на 'draft';

SELECT * FROM some_table_name WHERE profession LIKE '%draft';


# Получить все ряды, в которых значение столбца profession содержит 'draft';

SELECT * FROM some_table_name WHERE profession LIKE '%draft%';


# Получить все ряды, в которых значение столбца profession начинается с 'dr' и заканчивается на 'aft';

SELECT * FROM some_table_name WHERE profession LIKE 'dr%aft';

sql-in-wordpress-example-11.sql hosted with ❤ by GitHub

Подстановочный символ _

_ похож на %, но всегда подменяет только один символ. Поэтому, если вы зададите паттерн arch_tect, запрос вернет все строки, в которых значение указанной колонки может содержать следующее:

• architect
• archatect
• arch1tect
• archotect

Обратите внимание, что такой паттерн не найдет пустые или дублирующиеся символы, например archtect или archiitect.

Приведем некоторые примеры использования подстановочных символов _ :

# Получить все строки, в которых значение столбца profession содержит 'architect' только вместо 'i' может быть любой символ;

SELECT * FROM some_table_name WHERE profession LIKE 'arch_tect';


# Получим все строки, в которых значение столбца profession содержит 'architect', но начинается с произвольного символа;

SELECT * FROM some_table_name WHERE profession LIKE '_architect';


# Получим все строки, в которых значение столбца profession содержит 'architect', но заканчивается на произвольный символ;

SELECT * FROM some_table_name WHERE profession LIKE 'architect_';


# Получим все строки, в которых значение столбца profession содержит 'architect', но вместо 'i' и 'e' могут быть что угодно;

SELECT * FROM some_table_name WHERE profession LIKE 'arch_t_ct';

sql-in-wordpress-example-12.sql hosted with ❤ by GitHub

Чуть более сложный пример использования оператора LIKE

Теперь, когда мы понимаем принципы работы подстановочных символов % и _, мы сможем использовать их вместе с операторам LIKE внутри запросов WHERE.

# По-русски: Получим все строки, в которых значение столбца profession заканчивается либо на 'ologist', либо на 'iatrist', а значение столбца hourly rate меньше 200.

SELECT * FROM some_table_name
WHERE (profession LIKE '%ologist' OR profession LIKE '%iatrist')
AND hourly_rate < 200;

sql-in-wordpress-example-13.sql hosted with ❤ by GitHub

Заметьте, что в этом примере мы использовали 2 условия LIKE, каждое со своим подстановочным символом %, и объединили оператором OR. Также обратите внимание как мы разбили запрос на несколько строк — вы можете смело делать также при написании SQL и мы рекомендуем писать так всегда. Правильно отформатированный SQL гораздо легче читать и воспринимать.

Сортировка результатов с помощью ORDER BY

Используя ORDER BY, мы можем отсортировывать полученные результаты по возрастанию или в обратном порядке. Пример сортировки результата запроса по-возрастанию (ASC):

SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY hourly_rate ASC;

sql-in-wordpress-example-14.sql hosted with ❤ by GitHub

Поскольку порядок сортировки по возрастанию (ASC) используется по-умолчанию, мы можем сократить этот пример и получить тот же результат без указания порядка сортировки, например:

SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY hourly_rate;

sql-in-wordpress-example-15.sql hosted with ❤ by GitHub

А если нужно отсортировать по убыванию, просто используйте ключевое слово DESC вот так:

SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY hourly_rate DESC;

sql-in-wordpress-example-16.sql hosted with ❤ by GitHub

Также возможно сортировать по нескольким колонкам. При таком способе сортировка будет выполнена сначала по первой колонке, а затем, если есть совпадения значений между колонками — по второй, третьей и так далее. Вот как это делается:

# Сначала, отсортируем по значениям колонки profession, а затем по hourly rate, в обоих случаях по возрастанию.

SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY profession, hourly_rate;

sql-in-wordpress-example-17.sql hosted with ❤ by GitHub

У нас есть возможность контролировать направление сортировки для каждой из колонок, например:

# Сначала отсортируем по колонке profession в убывающем порядке, затем по колонке hourly_rate в возрастающем порядке и в конце - по имени (name) в убывающем.

SELECT * FROM some_table_name WHERE hourly_rate > 50 ORDER BY profession DESC, hourly_rate ASC, name DESC;

sql-in-wordpress-example-18.sql hosted with ❤ by GitHub

SQL запросы с помощью wpdb — главного класса для работы с базой данных WordPress

wpdb — это встроенная в ядро WordPress абстракция для работы с базой данных. Она предоставляет разработчикам удобные методы для подготовки и безопасного выполнения прямых SQL запросов.

WordPress дает нам возможность доступа к экземпляру класса wpdb через глобальную переменную $wpdb. Просто объявив глобальную переменную $wpdb в вашей собственной функции, методе или классе вы сразу же можете использовать тот же экземпляр класса wpdb, который используется ядром WordPress.

<?php

function get_some_data(){
global $wpdb;
return $wpdb->get_results("SELECT * FROM {$wpdb->prefix}some_table WHERE some_column = 'some value'");

}

sql-in-wordpress-example-19.php hosted with ❤ by GitHub

Замечание про $wpdb->prefix

Обратите внимание на строку в примере выше, в которой используется $wpdb->prefix — это сделано для того, чтобы ваш запрос работал на любом сайте, независимо от того какой префикс используется в текущей базе данных. Рекомендуется всегда использовать $wpdb->prefix вместо хардкода стандартного префикса wp_ потому что очень часто разные хостинг-провайдеры меняют префикс баз данных по-умолчанию. Если вы думаете, что префикс всегда будет wp_ и выпустите плагин, использующий прямые запросы в базу, вас будет ждать очень неприятный сюрприз вскоре после появления первых активных установок плагина.

Будьте осторожны с динамическими данными — это всегда риск

Когда вы пишете свои SQL запросы, часто может быть так, что они зависят от вводимых пользователями данных (например, из формы) или от результата выполнения некоего PHP кода. Такие данные называются динамическими.

Динамические данные непредсказуемы и в целом считаются небезопасными, а, значит, нуждаются в дополнительной «очистке» перед тем как ими можно пользоваться в MySQL. Мы рассмотрим практики, с помощью которых можно безопасно управляться с динамическими данными пользователей и защитить ваши запросы от SQL инъекций.

Защита от атак с использованием SQL-инъекций

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

Цель подобных атак заключается в изменении (подмене) реального запроса в базу данных для получения доступа к данным, закрытым от внешнего доступа. Или же для получения доступа ко всему веб-приложению, в том числе для удаления данных.

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

К счастью для нас, встроенный в ядро WordPress класс wpdb имеет удобный метод wpdb::prepare(), который берет на себя экранирование и, таким образом, защищает базу от SQL инъекций. Вам однозначно стоит хорошо изучить метод wpdb::prepare() потому что использовать его вы будете часто.

Подготовка SQL команд с использованием метода wpdb::prepare()

Синтаксис этого метода напоминает PHP-функцию sprintf(). Метод принимает (как аргументы функции) SQL запрос, содержащий любое количество переменных для экранирования. Переменные являются заменителями реальных данных и используются согласно следующим правилам:

• %d для экранирование чисел
• %f для экранирования чисел с плавающей запятой
• %s для экранирования строк

Приведу простой пример подготовки SQL запроса с одной переменной. Обратите внимание на использование

Here’s a simple example of preparing an SQL statement using one parameter. Note the use %s вместо строки, которая подлежит экранированию:

<?php

global $wpdb;

$sql = $wpdb->prepare( 
"SELECT * 
FROM {$wpdb->prefix}some_table 
WHERE some_column = %s", 
'some user submitted string' 
);

sql-in-wordpress-example-20.php hosted with ❤ by GitHub

Теперь рассмотрим пример с двумя переменными внутри нашего SQL запроса. В этом примере метод prepare()просто продолжает принимать наши переменные как аргументы функции.

<?php

global $wpdb;

// Представим, что значения этих переменных были получены от пользователя или являются результатом работы сторонней функции.
$int_val = 1;
$float_val = 3.14159;

$prepared_sql = $wpdb->prepare(
"SELECT * FROM {$wpdb->prefix}some_table WHERE some_column BETWEEN %d AND %f",
$int_val,
$float_val
);

sql-in-wordpress-example-21.php hosted with ❤ by GitHub

Эти же переменные можно передавать в виде массива, метод обработает их точно также. Вот вариант с теми же исходными данными, что и в примере выше, только с использованием массива:

<?php

global $wpdb;

// Представим, что значения этих переменных были получены от пользователя или являются результатом работы сторонней функции.
$int_val = 1;
$float_val = 3.14159;

$prepared_sql = $wpdb->prepare(
"SELECT * FROM {$wpdb->prefix}some_table WHERE some_column BETWEEN %d AND %f", [
$int_val,
$float_val
]
);

sql-in-wordpress-example-22.php hosted with ❤ by GitHub

Пример того, как не надо строить запросы

Следующий код может выглядеть достаточно невинно на первый взгляд, но с учетом того, что мы уже знаем, можно предположить потенциальную уязвимость запроса перед SQL инъекциями через переменную $value.

<?php

function get_some_data( $value ){
global $wpdb;
return $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}some_table WHERE some_column = '{$value}'" );
}

sql-in-wordpress-example-23.php hosted with ❤ by GitHub

Обезопасим код с помощью подготовки запроса

Попробуем использовать wpdb::prepare() метод для защиты того же запроса против SQL инъекций. Функция в примере предварительно передает переменную в объект wpdb, который, в свою очередь, экранирует содержимое переменной перед тем как отправлять ее в SQL запрос. Таким образом достигается безопасность работы функции.

<?php

function get_some_data( $value ){

global $wpdb;
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE some_column = %s", $value );
return $wpdb->get_results( $sql );

}

sql-in-wordpress-example-24.php hosted with ❤ by GitHub

Если посмотреть исходный код ядра, то можно увидеть что класс wpdb использует встроенную в PHP функцию mysql_real_escape_string() для экранирования специальных символов и vsprintf() для непосредственного выполнения замен. Конечно, там еще много чего происходит и практически всегда будет разумнее использовать встроенные методы ядра WordPress, а не писать собственные «костыли», но не помешает и разобраться в принципах работы используемых методов.

Подстановочный символ % и подготовка запросов

При работе с подстановочными символами % внутри SQL команд стоит учитывать некоторые нюансы. Например, помнить о том, что wpdb::prepare() может спутать ваши подстановочные символы с параметрами самого SQL запроса. То есть, если wpdb::prepare() обнаружит два % символа в запросе, он будет ожидать получения двух наборов данных для подготовки, экранирования и вставки в этот запрос. Давайте рассмотрим это на примере:

<?php

global $wpdb;

$value = 'Данные для экранирования';

$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s AND column_b LIKE '%some_string'", $value );

sql-in-wordpress-example-25.php hosted with ❤ by GitHub

В этом примере запрос не будет выполнен а вы получите примерно такой нотис в PHP логах:

PHP Notice: wpdb::prepare was called <strong>incorrectly</strong>. The query does not contain the correct number of placeholders (2) for the number of arguments passed (1).

В переводе: wpdb::prepare был вызван неправильно. Запрос не получил нужного количества аргументов (2) для заполнения ожидаемого количества заполнителей (2). Всего передано аргументов: 1.

Это произошло потому что строка запроса, в которой мы используем второй оператор LIKE, содержит %s. Мы используем его с целью получить любые строки, заканчивающиеся на ‘some_string’, но вместо этого метод wpdb::prepare() интерпретирует это как второй параметр под экранирование и замену, только для него отсутствуют данные. В таких случаях лучше всего будет вынести все строки, содержащие символ % в аргументы метода, вот так:

<?php
global $wpdb;

$value = 'Значение для экранирования';

$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s AND column_b LIKE %s", $value, '%some_string' );

sql-in-wordpress-example-26.php hosted with ❤ by GitHub

Это сэкономит нам время на отладку ошибки запроса и WordPress получит правильное количество данные для подготовки к передаче в запрос — в нашем случае, 2.

Особые случаи использования % и _ в качестве символов строк и метод wpdb::esc_like()

Как мы уже убедились в предыдущем разделе, подстановочный символ % может быть интерпретирован как параметр запроса в базу, что обычно заканчивается ошибками в запросах. Этого можно избежать, поместив строки, содержащие такие символы, в переменные для замены.

Однако, при использовании оператора LIKE мы будем сталкиваться с ситуациями, когда подстановочные символы не будут являться таковыми, а будут обычными символами процентов или нижних подчеркиваний. И нам нужно будет убедиться, что никакая функция или метод не считает их чем-то бОльшим, чем обычные строковые символы. Вот примеры таких ситуаций:

1. Вам нужно вставить % или _ как простые символы внутри строго заданного SQL запроса
2. Нужно обработать запрос от пользователя, который ищет что-то вроде 90%?
3. Вы обращаетесь к сторонней функции, которая возвращает мета-ключи, содержащие символы _ ?

При подстановке этих значений в запрос с параметрами, wpdb::prepare() будет работать с ними, как с обычными подстановочными символами (wildcards). Например:

<?php

global $wpdb;
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s", '_value_%' );

sql-in-wordpress-example-27.php hosted with ❤ by GitHub

Такой запрос в реальности вернет строки, в которых столбец column_a начинается, буквально, с такого: «любой единичный символ, за которым следует слово value, за которым есть еще один символ». avaluezwhatever, например, точно подойдет под такой запрос. Но это будет не то, что нам нужно, правда ведь? Что мы в действительности хотим получить: все, что буквально начинается с ‘_value_’. Для этого потребуется использовать другой метод класса wpdb — wpdb::esc_like()

<?php

global $wpdb;
$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s", $wpdb->esc_like('_value_%') );

sql-in-wordpress-example-28.php hosted with ❤ by GitHub

Метод wpdb->esc_like() умеет экранировать символы % and _ таким образом, что они могут быть использованы как буквальные символы (строковые литералы) внутри SQL запроса.

<?php
global $wpdb;

// Переменная $dynamic_input может быть получена из формы или быть результатом обращения к сторонней функции
$value = '%' . $wpdb->esc_like( $dynamic_input ) . '%';

$sql = $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}some_table WHERE column_a LIKE %s", $value );

sql-in-wordpress-example-29.php hosted with ❤ by GitHub

---

Оригинал статьи: https://hookturn.io/2020/12/custom-wordpress-sql-queries-for-beginners/