Защищают ли подготовленные выражения/переменные полностью от SQL инъекций?

$dbh = new PDO("test");    
$stmt = $dbh->prepare('SELECT * FROM users where username = :username');
$stmt->execute(array(':username' => $_REQUEST['username']));

$dbh = new PDO("test");  
$sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < ? AND color = ?');
$sth->execute(array($_POST['number'], $_POST['color']));

Безопасность

Если говорить о числовых и строковых литералах в запросе - то да, защищают.

Этого достаточно и ничего больше не надо делать?

В общем случае - ничего. В случае с ПДО также желательно еще выставлять кодировку соединения в DSN, но это в любом случае нужно делать.

Идея подготовленных выражений не в том, чтобы отправить данные и запрос отдельно, а в том, чтобы добавлением данных в запрос занимался не программист, а драйвер БД. А уж как оно у него там внутри реализовано - дело десятое.

Примечание
По настоянию уважаемого vp_arth я должен добавить, что существует теоретическая возможность так специально настроить свою систему, чтобы она пропускала инъекции в режиме эмуляции. Для этого потребуется две вещи:

• специальным образом настроить mysql, задав режим NO_BACKSLASH_ESCAPES
• использовать двойные кавычки вместо одинарных в качестве ограничителей строк

Это был баг в mysql, который был исправлен только недавно. Подробнее можно почитать в этом посте на SO.

Ограничения

Куда интереснее здесь вопрос, что делать, когда подготовленные выражения использовать невозможно. Как говорилось выше, параметры в запросе можно использовать дла замены только строковых или числовых литералов. Но бывают случаи, когда в запрос надо подставить не данные, а имя столбца. Вот пример такой ситуации с разбором неправильных решений (по-английски): An SQL injection against which prepared statements won't help.
Ситуация нечастая, но о ней надо знать и быть к ней готовым.

Удобство

В конце концов, подготовленные выражения просто удобнее. Сравним олд скул

$name = $mysqli->real_escape_sring($_GET['name']);
$price = $mysqli->real_escape_sring($_GET['price']);
$color = $mysqli->real_escape_sring($_GET['color']);
$sql = "SELECT * FROM goods WHERE name='$name' and color='$color' and price > '$price'";
$res = $mysqli->query($sql);

и PDO prepared statements

$stmt = $pdo->prepare("SELECT * FROM goods WHERE name = ? and color = ? and price > ?");
$stmt->execute([$_GET['name'],$_GET['price'],$_GET['color']]);

-- компактно, аккуратно и безопасно.

Мифы про экранирование

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

Пример такого нецелевого использования приведен по ссылке выше, но можно привести и другой, совсем уж дурацкий но от этого еще более наглядный:

$id = $mysqli->real_escape_string($_GET['id']);
$sql = "SELECT * FROM table WHERE id=$id";

Если думать, что функция служит "для защиты от инъекций", то этот код логичен. Однако в реальности он дает возможность приписать дальше практически любой запрос через UNION и получить классическую инъекцию.

При этом не надо ударяться и в другую крайность - примененная по назначению, для экранирования спецсимволов в строках, mysqli_real_escape_string прекрасно справляется с инъекциями, просто в качестве побочного эффекта.

Если база поддерживает раздельную передачу запроса и данных, а драйвер базы данных использует эту фичу - вы в безопасности.

Данные переданные отдельно от запроса никак не могут повлиять на исходный запрос.

Однако, есть такое понятие, как "эмулированные подготовленные выражения"(Emulated Prepared Statements).
Они используются, если база не поддерживает реальных prepared statements, либо если мы сознательно включили эту "фичу"(в некоторых драйверах она может быть включена по умолчанию).

В случае эмуляции, база данных не знает, где данные, а где запрос. За экранирование данных мы всецело полагаемся на реализацию этой эмуляции внутри конкретного драйвера(pdo_firebird, pdo_mysql).

Так что, если вы ещё не убедились в наличии этого параметра подключения, убедитесь и отключите его явно:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);  

Если вам необходимо использовать пользовательские данные не как данные, а скажем, как имя таблицы/поля - единственным способом обезопасить себя на 100% - белые списки.
Никогда не используйте пользовательские данные в запросе напрямую, берите нужные вставки в запрос из списка разрешённых для этого вставок.

  $i = array_search($_GET['field'], User::$orderFields, true);
  $orderField = $i === false ? User::$defaultOrderField : User::$orderFields[$i];
  $sql = "... ORDER BY $orderField";  

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

Из обсуждения под соседним вопросом:

@vp_arth: На самом деле, интереснее был бы пример со строками в двойных кавычках в запросе, в то время, как real_escape_string по умолчанию заточена под апострофы

Имелся в виду вот этот пример:

/* my.ini
[mysqld]
sql-mode="NO_BACKSLASH_ESCAPES";
Или вызов из php:*/
$db->query('SET SQL_MODE="NO_BACKSLASH_ESCAPES"');
$input = '" OR id = "1';
$input = $db->real_escape_string($input);
echo 'SELECT * FROM Users WHERE login = "'.$input.'"';
// SELECT * FROM Users WHERE login = "" OR id = "1"

@vp_arth: защищающие не больше, чем вышеупомянутый real_escape_string

Имелось в виду следующее утверждение из вопроса:

@Ипатьев: Эта конструкция выполняет строго определенную и очень специализированную синтаксическую функцию

В случае эмуляции подготовленных выражений, они также "выполняют строго определенную и очень специализированную синтаксическую функцию", которую в общем случае можно обойти
синтетический пример
В реальной жизни сталкивался с тем, что emulated prepared statements в pdo_firebird могут падать с Segmentation fault в зависимости от количества байт в utf-8 строках.

Кратко

Механизм prepared statements в контексте безопасности только обеспечит корректную передачу самого запроса и значений параметров для него из кода в СУБД. Не меньше, но и не больше. Отстрелить ногу всё так же возможно. Но это становится сильно труднее, чем если пытаться подставлять данные сразу в запрос.

Чтобы ответить на вопрос безопасности надо сначала понять, в чём опасность.

Что такое sql-инъекции и как они происходят?

$pdo->query("select * from users where login = '" . $login ."' 
    and pass_md5 = '" . md5($pass) . "'");

Что здесь видит разработчик? Подстановку данных в строку. С точки зрения PHP здесь ничего опасного нет. С точки зрения неопытного PHP-разработчика, проверяющего свой код - код работает, ему ведь не пришло в голову написать что-то странное в логин.

Приключения начинаются, когда кто-то вместо логина вводит admin' or '1'='1. Здесь необходимо напомнить, что SQL - штука изначально текстовая. Что после конкатенации на PHP получит СУБД?

select * from users where login = 'admin' or '1'='1' 
    and pass_md5 = 'какой-тоmd5'

Это одна строка непрерывного текста. Как СУБД должна понять, что этот запрос отличается от задуманного? Это запрос, он синтаксически корректен, его можно выполнить - СУБД его и выполняет. Но запрос уже делает не то, что хотел сказать разработчик.

Опасность и распространённость SQL-инъекции именно в текстовой сущности запроса. Очень просто подставить в нужное место переменную с данными - но это путь к ошибке и так делать нельзя.

Теперь защита.

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

select * from users where login = 'admin\' or \'1\'=\'1' 
    and pass_md5 = 'какой-тоmd5'

Теперь парсер понимает, где в строке логина кончаются данные. При этом критично важна согласованность кодировок клиента и сервера, иначе в некоторых случаях можно и пробить.

С другой стороны к вопросу подходит механизм prepared statements: у нас есть конечное число запросов на приложении, но использующие разные данные. Поэтому для prepared statements решили явным образом разделить структуру запроса и данные. Теперь вместо ситуации "привет, выполни вот этот запрос" приложение через специальный протокол говорит СУБД "привет, приготовься выполнить вот такой запрос", а затем: "помнишь вот тот запрос готовили? Теперь выполни его и используй в качестве первого параметра - эти следующие 8 байт, а для второго параметра - следующие 20 байт". Данные для запроса передаются физически отдельно от структуры запроса, поэтому СУБД в принципе не может перепутать данные с запросом, даже при том, что никакого экранирования не происходит. Важный момент - именно поэтому через механизм prepared statemenets невозможно изменить структуру запроса, даже банально изменить направление сортировки order by.

Данные будут переданы как есть без искажений ни самих данных, ни структуры запроса, всё хорошо.

Вот только достаточно ли этого?

Сначала стоит сказать про по-умолчанию включенную эмуляцию подготовленных выражений. Это не страшно, пока у вас корректно установлена кодировка соединения и даёт некоторые бонусы. А вот если кодировка стоит неверно - то атака на подобии указанной выше через кодировку вновь реальна.

С PDO::ATTR_EMULATE_PREPARES в значении true за обработку подготовленных выражений отвечает сам PDO. В базу данных передаётся чистый запрос с уже подставленными и корректно экранированными данными (только в dsn не забывайте правильно charset указывать).

PDO::ATTR_EMULATE_PREPARES в значении false именно использует штатный механизм СУБД для подготовки запроса и затем отдельным обращением передаёт данные для этого запроса. Т.е. нормальные, реальные подготовленные выражения.

Из не очевидных моментов:

• для сложных запросов дико удобно использовать один и тот же именованный параметр несколько раз в запросе. Какой-нибудь where user_from = :id or user_to = :id и передать в списке параметров id только один раз. На самом деле зависит от реализации конкретного драйвера, например, в mysql такой запрос при выключенной эмуляции завершится ошибкой Invalid parameter number, но он же будет корректно исполнен в PostgreSQL. С включенной эмуляцией - соответственно можно использовать вне зависимости от СУБД.
• специфика конкретных СУБД. Какие-то СУБД из тех, что умеет PDO могут не уметь подготовленные выражения. Например, очень популярный PgBouncer (пул коннектов для PostgreSQL) не умеет обрабатывать подготовленные выражения. С эмуляцией выражений можно в коде проекта пользоваться удобствами API с prepare
• вопрос с тем, что подготовленный запрос разбирается и строит план один раз и затем только выполняется - на самом деле гораздо сложнее. Тот же mysql сохраняет запрос только в рамках соединения. Поэтому в типичном сценарии использования "подготовил, выполнил, закрыл соединение" никаких плюсов реальное препарирование не даёт. А кэширование плана между соединениями - по-моему (сам с этой СУБД не работал), есть в Oracle и приносит некоторое количество головной боли, ведь оптимальный план запроса в немалой степени зависит от самих данных.

Проиллюстрирую головную боль с кэшированием плана запроса на простой очереди. В колонке статус только десяток записей в статусе waiting, но несколько миллионов в статусе done. В базу приезжает запрос:

select /**/ from tablename where status = ? order by id limit ?

Спрашивается, как без самих данных база угадает оптимальный план? Если ей приедет запрос на status = waiting, оптимально идти по индексу status. Если придёт запрос в статусе done - есть ещё несколько вариантов: при малом лимите имеет смысл пойти по индексу id и просто выкидывать записи с неподходящим статусом.

И вновь включаем голову

Есть, например, запрос:

$stmt = $pdo->prepare('update users_balance 
    set balance = balance - :amount 
    where user_id = :uid and balance > :amount');
$stmt->execute([
    'uid' => $userId,
    'amount' => $_POST['payment'],
]);

Безопасно ли так оставить? Нет. Передайте в payment отрицательное число и вы без проблем получите начисление денег вместо списания. Логика запроса не нарушена, данные передаются как есть, не искажаются - но бизнес логика приложения сломана.

Поэтому данные проверять вы всё равно обязаны. И никакой серебряной пули вам не будет - именно вы знаете, и именно в конкретном месте кода - должно ли дальше быть только отрицательное число. Или строка логина или ещё что-нибудь. А механизм prepared statements только обеспечит корректный транспорт самого запроса и значений из кода в СУБД и не более того.

Здесь же стоит сказать про то же самое изменение сортировки запроса. Механизм prepared statements вам это сделать не позволит, но вы заранее знаете, что направлений сортировки есть только два: asc или desc, а так же заранее знаете, по каким полям выборку можно сортировать. Поэтому направление сортировки элементарно проверяется по белому списку возможных значений и проблемы не представляет.

Ещё чуть-чуть о производительности

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

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE ( subsidiary_id    = :sub_id OR :sub_id IS NULL )
   AND ( employee_id      = :emp_id OR :emp_id IS NULL )
   AND ( UPPER(last_name) = :name   OR :name   IS NULL )

И чтобы не использовать какое-то из условий поиска достаточно его параметр просто указать как NULL. Да, на парсере запроса мы сэкономили, зато предельно усложнили жизнь оптимизатору. Не имея реальный данных запроса оптимизатор вынужден использовать только последовательное чтение всей таблицы. Но если параметризованный запрос заменить литералами значений - то оптимизатор вполне хорошо понимает, что от него хотят и использует более внятный план. Т.е. таким запросом через prepared statements мы не улучшили производительность приложения, а убили её в корне.

Подготовленные запросы

1. повышают безопасность,
2. избавляют от необходимости экранировать кавычки и другие спецсимволы,
3. избавляют от необходимости заключать вставляемые строковые значения в кавычки вообще,
4. повышают быстродействие за счет кеширования плана выполнения запроса на стороне сервера,
5. повышают читаемость кода.

Поэтому я один из тех, кто агитирует за повсеместное использование этого подхода.

Если говорить про шаманство, которое приходится выполнять вне зависимости от способа выполнения запроса, — то это устранение HTML-разметки из данных, поступающих от пользователя (http://www.php.net/Strip_tags). Не всегда это дейтсвительно необходимо, но в тех случаях, когда, например, сохраняются сообщения на форуме, игнорирование этой функции может привести к нарушению разметки всей страницы или к выполнению нежелательного js-кода у всех пользователей, открывших страницу. Но к выполнению запросов это напрямую не относится.

Видел как на многих самописных движках берут и тупо из $_POST экранируют через PDO, и кидают в базу. Да, все окей вроде-бы. НО.

Но тут нас ждет XSS!

Строка вида

<script>console.log( document.cookie ) </script>

Даже при экранировании через PDO так и попадет в базу, а потом мучайся, где и как накосячил.