Как в sql запрос вставить переменную php

Как в sql запрос вставить переменную php

Для добавления данных в БД MySQL применяется sql-команда INSERT , которая имеет следующий синтаксис:

Данная команда также выполняется методом exec() объекта PDO. Стоит отметить, что для sql-команд INSERT , UPDATE и DELETE метод exec() возвращает количество затронутных командой строк (добавленных, измененных или удаленных). Таким образом, мы можем узнать сколько строк было добавлено.

Сначала рассмотрим простейшее добавление одного объекта в БД. Для примера возьмем созданную в прошлой теме базу данных «testdb1» и созданную в ней таблицу Users со следующим определением:

И для добавления определим следующий скрипт PHP:

Команда на добавление здесь выглядит следующим образом:

То есть в столбец name добавляется строка «Tom», а в столбец age — число 37. Для столбца id не добавляется никакого значения, потому что при создании таблицы для него указан параметр AUTO_INCREMENT — то есть значение этого столбца у каждой добавляемой строки будет автоматически увеличиваеться по сравнению с предыдущей на единицу.

При добавлении мы получаем количество добавленных строк в переменую $affectedRowsNumber и затем выводим ее значение в браузере. Поэтому при успешном добавлении мы увидим

Множественное добавление

Также мы можем добавить сразу несколько объектов:

Здесь в таблицу добавляется три строки. Соответственно в браузере мы увидим:

Добавление данных из формы HTML

В большинстве случаев добавляемые данные будут приходить из вне, например, присылаться в запросе пользователя. Рассмотрим добавление данных, отправленных из формы HTML. Для этого определим следующий скрипт:

Здесь мы проверяем, пришли ли с сервера данные в POST-запросе, которые имеют ключи «username» и «userage»:

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

Если была добавлена строка, то есть метод exec() возвратил число больше нуля, то выводим пользователю соответствующее сообщение.

После кода php собственно определена форма на добавление данных с помощью post-запроса.

Запустим скрипт. Введем в форму данные и нажмем на кнопку:

И данные будут добавлены. Вроде все хорошо. Однако здесь есть большой недостаток.

Параметризация запросов

Недостаток выше приведенного скрипа заключается в том, что мы никак не констролируем присылаемые данные и сохраняем их в базу данных как есть. Что несет потенциальную угрозу безопасности, особенно при добавлении строк типа «; DELETE FROM `Users`; — . Кроме того, в ряде случае может быть проблематично добавить даже безопасные данные, например, строку, которая содержит одинарную кавычку, типа «Tom O’Brian».

Для решения этих проблем PDO предлагает параметризацию запросов с помощью применения заранее подготовленных выражений — prepared statement . Выражения prepared statement вместо жестко установленных значений или переменных принимают параметры, которые не привязаны к конкретным значениям. Эти выражения prepared statement посылаются серверу базы данных до того, как станут известны используемые данные, что позволяет серверу приготовить их к выполнению, но при этом они не выполняются. А когда пользователь присылает данные — параметры заменяются пришедшими данными, и выражение prepared statement выполняется.

Перепишем предыдущий пример с использованием параметров:

В SQL-выражении теперь применяются параметры:

:username и :userage — это названия параметров. Причем они начинаются с символа двоеточия :.

Само выражение prepared statement создается с помощью метода prepare() объекта PDO, в который передается выполняемая sql-команда:

Фактически здесь создается объект PDOStatement , который сохраняется в переменную $stmt .

Чтобы связать параметр с конкретным значением у объекта PDOStatement вызывается метод bindValue() . Первый параметр этого метода — собственно параметр из sql-команды, а второй параметр — передаваемое ему значение.

Так, в данном случае параметр :username привязывается к значению из $_POST[«username»]

Причем привязка может производиться и к конкретным значениям и обычным переменным, например:

Для выполнения sql-выражения у объекта PDOStatement вызывается метод execute() , который для команды INSERT возвращает число добавленных строк.

Передача значений параметрам через массив по имени

В примере выше для параметризации применялся метод bindValue() :

Но есть и другой способ привязки параметров к значениям — мы можем передать в метод execute() параметры и их значения в виде ассоциативного массива:

В этом случае названия параметров являются ключами.

Передача значений параметрам через массив по позиции

Третий способ привязки значений к параметрам представляет передачу значений по позиции:

В этом случае вместо названий параметров применяются знаки вопроса ? . Для передачи этим параметрам значений в метод execute() также передается массив. Первое значение массива привязывается к первому параметру (условно добавляется вместо первого знака вопроса), второе значение привязывается ко второму параметру и т.д.

How to include a PHP variable inside a MySQL statement

I’m trying to insert values in the contents table. It works fine if I do not have a PHP variable inside VALUES. When I put the variable $type inside VALUES then this doesn’t work. What am I doing wrong?

5 Answers 5

The rules of adding a PHP variable inside of any MySQL statement are plain and simple:

1. Use prepared statements

This rule covers 99% of queries and your query in particular. Any variable that represents an SQL data literal, (or, to put it simply — an SQL string, or a number) MUST be added through a prepared statement. No exceptions.

This approach involves four basic steps

• in your SQL statement, replace all variables with placeholders
• prepare the resulting query
• bind variables to placeholders
• execute the query

And here is how to do it with all popular PHP database drivers:

Adding data literals using mysqli

The code is a bit complicated but the detailed explanation of all these operators can be found in my article, How to run an INSERT query using Mysqli, as well as a solution that eases the process dramatically.

For a SELECT query you will need to add just a call to get_result() method to get a familiar mysqli_result from which you can fetch the data the usual way:

Adding data literals using PDO

In PDO, we can have the bind and execute parts combined, which is very convenient. PDO also supports named placeholders which some find extremely convenient.

2. Use white list filtering

Any other query part, such as SQL keyword, table or a field name, or operator — must be filtered through a white list.

Sometimes we have to add a variable that represents another part of a query, such as a keyword or an identifier (a database, table or a field name). It’s a rare case but it’s better to be prepared.

In this case, your variable must be checked against a list of values explicitly written in your script. This is explained in my other article, Adding a field name in the ORDER BY clause based on the user’s choice:

Unfortunately, PDO has no placeholder for identifiers (table and field names), therefore a developer must filter them out manually. Such a filter is often called a "white list" (where we only list allowed values) as opposed to a "black-list" where we list disallowed values.

So we have to explicitly list all possible variants in the PHP code and then choose from them.

Here is an example:

Exactly the same approach should be used for the direction,

After such a code, both $direction and $orderby variables can be safely put in the SQL query, as they are either equal to one of the allowed variants or there will be an error thrown.

The last thing to mention about identifiers, they must be also formatted according to the particular database syntax. For MySQL it should be backtick characters around the identifier. So the final query string for our order by example would be

Как в sql запрос вставить переменную php

СУБД MySQL поддерживает подготавливаемые запросы. Подготавливаемые (или параметризованные) запросы используются для повышения эффективности, когда один запрос выполняется многократно и защищает от SQL-инъекций.

Выполнение подготавливаемого запроса проводится в два этапа: подготовка и исполнение. На этапе подготовки на сервер посылается шаблон запроса. Сервер выполняет синтаксическую проверку этого шаблона, строит план выполнения запроса и выделяет под него ресурсы.

MySQL сервер поддерживает неименованные, или позиционные, псевдопеременные ? .

За подготовкой следует выполнение. Во время выполнения клиент связывает значения параметров и отправляет их на сервер. Сервер выполняет запрос со связанными значениями, используя ранее созданные внутренние ресурсы.

Пример #1 Подготовленный запрос

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «example.com» , «user» , «password» , «database» );

/* Неподготовленный запрос */
$mysqli -> query ( «DROP TABLE IF EXISTS test» );
$mysqli -> query ( «CREATE TABLE test(id INT, label TEXT)» );

/* Подготовленный запрос, шаг 1: подготовка */
$stmt = $mysqli -> prepare ( «INSERT INTO test(id, label) VALUES (?, ?)» );

/* Подготовленный запрос, шаг 2: связывание и выполнение */
$id = 1 ;
$label = ‘PHP’ ;
$stmt -> bind_param ( «is» , $id , $label ); // «is» означает, что $id связывается, как целое число, а $label — как строка

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

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

Пример #2 Выражение INSERT один раз подготавливается, а затем многократно выполняется

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «example.com» , «user» , «password» , «database» );

/* Неподготовленный запрос */
$mysqli -> query ( «DROP TABLE IF EXISTS test» );
$mysqli -> query ( «CREATE TABLE test(id INT, label TEXT)» );

/* Подготовленный запрос, шаг 1: подготовка */
$stmt = $mysqli -> prepare ( «INSERT INTO test(id, label) VALUES (?, ?)» );

/* Подготовленный запрос, шаг 2: связывание и выполнение */
$stmt -> bind_param ( «is» , $id , $label ); // «is» означает, что $id связывается, как целое число, а $label — как строка

$data = [
1 => ‘PHP’ ,
2 => ‘Java’ ,
3 => ‘C++’
];
foreach ( $data as $id => $label ) <
$stmt -> execute ();
>

$result = $mysqli -> query ( ‘SELECT id, label FROM test’ );
var_dump ( $result -> fetch_all ( MYSQLI_ASSOC ));

Результат выполнения данного примера:

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

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

Также имеет смысл рассмотреть SQL-синтаксис вставки множества значений в выражении INSERT. В примере выше мультивставка (значения для вставки перечисляются через запятую) в предложении INSERT обошлась бы дешевле, чем подготовленный запрос.

Пример #3 Меньше обменов данными при использовании мультивставок SQL

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «example.com» , «user» , «password» , «database» );

$mysqli -> query ( «DROP TABLE IF EXISTS test» );
$mysqli -> query ( «CREATE TABLE test(id INT)» );

$values = [ 1 , 2 , 3 , 4 ];

$stmt = $mysqli -> prepare ( «INSERT INTO test(id) VALUES (?), (?), (?), (?)» );
$stmt -> bind_param ( ‘iiii’ , . $values );
$stmt -> execute ();

Типы данных значений в результирующей таблице

В протоколе клиент-серверного взаимодействия MySQL для обычных и подготавливаемых запросов определены разные протоколы передачи данных клиенту. Параметризованные запросы используют так называемый двоичный протокол. Сервер MySQL посылает результирующий набор клиенту "как есть" в двоичном формате. Данные в таблице не преобразуются в текст. Клиентские библиотеки получают двоичные данные и пытаются преобразовать значения в соответствующие типы данных PHP. Например, столбец результатов запроса типа SQL INT PHP примет и преобразует в тип integer.

Пример #4 Исходные типы данных

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «example.com» , «user» , «password» , «database» );

/* Неподготовленный запрос */
$mysqli -> query ( «DROP TABLE IF EXISTS test» );
$mysqli -> query ( «CREATE TABLE test(id INT, label TEXT)» );
$mysqli -> query ( «INSERT INTO test(id, label) VALUES (1, ‘PHP’)» );

$stmt = $mysqli -> prepare ( «SELECT id, label FROM test WHERE > );
$stmt -> execute ();
$result = $stmt -> get_result ();
$row = $result -> fetch_assoc ();

printf ( «id = %s (%s)\n» , $row [ ‘id’ ], gettype ( $row [ ‘id’ ]));
printf ( «label = %s (%s)\n» , $row [ ‘label’ ], gettype ( $row [ ‘label’ ]));

Результат выполнения данного примера:

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

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

Результаты из подготовленного запроса можно получить либо привязав выходные переменные, либо запросив объект mysqli_result .

Выходные параметры нужно привязывать после выполнения запроса. Каждому столбцу результирующей таблицы должна соответствовать ровно одна переменная.

Пример #5 Привязка переменных к результату запроса

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «example.com» , «user» , «password» , «database» );

/* Неподготовленный запрос */
$mysqli -> query ( «DROP TABLE IF EXISTS test» );
$mysqli -> query ( «CREATE TABLE test(id INT, label TEXT)» );
$mysqli -> query ( «INSERT INTO test(id, label) VALUES (1, ‘PHP’)» );

$stmt = $mysqli -> prepare ( «SELECT id, label FROM test WHERE > );
$stmt -> execute ();

$stmt -> bind_result ( $out_id , $out_label );

while ( $stmt -> fetch ()) <
printf ( «id = %s (%s), label = %s (%s)\n» , $out_id , gettype ( $out_id ), $out_label , gettype ( $out_label ));
>

Результат выполнения данного примера:

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

Также можно буферизовать данные результирующих таблиц подготовленного запроса с помощью функции mysqli_stmt::store_result() .

Извлечение результатов запроса посредством mysqli_result интерфейса

Вместо использования привязки переменных к результатам запроса, результирующие таблицы можно извлекать средствами интерфейса mysqli_result. Функция mysqli_stmt::get_result() возвращает буферизованный результирующий набор строк.

Пример #6 Использование mysqli_result для выборки результатов запроса

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «example.com» , «user» , «password» , «database» );

/* Неподготовленный запрос */
$mysqli -> query ( «DROP TABLE IF EXISTS test» );
$mysqli -> query ( «CREATE TABLE test(id INT, label TEXT)» );
$mysqli -> query ( «INSERT INTO test(id, label) VALUES (1, ‘PHP’)» );

$stmt = $mysqli -> prepare ( «SELECT id, label FROM test WHERE > );
$stmt -> execute ();

$result = $stmt -> get_result ();

var_dump ( $result -> fetch_all ( MYSQLI_ASSOC ));

Результат выполнения данного примера:

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

Пример #7 Буферизация результирующего набора для удобства чтения данных

mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «example.com» , «user» , «password» , «database» );

/* Неподготовленный запрос */
$mysqli -> query ( «DROP TABLE IF EXISTS test» );
$mysqli -> query ( «CREATE TABLE test(id INT, label TEXT)» );
$mysqli -> query ( «INSERT INTO test(id, label) VALUES (1, ‘PHP’), (2, ‘Java’), (3, ‘C++’)» );

$stmt = $mysqli -> prepare ( «SELECT id, label FROM test» );
$stmt -> execute ();

$result = $stmt -> get_result ();

for ( $row_no = $result -> num_rows — 1 ; $row_no >= 0 ; $row_no —) <
$result -> data_seek ( $row_no );
var_dump ( $result -> fetch_assoc ());
>

Результат выполнения данного примера:

Экранирование и SQL-инъекции

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

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

Эмуляция подготовленного запроса на клиенте

В API нет возможности эмулировать подготавливаемые запросы на клиенте.

Быстрое сравнение подготовленных и неподготовленных запросов

В таблице ниже приведено сравнение обычного и подготовленного на сервере запросов.

Как вставить переменные в sql запрос на php?

Вопрос:
Как правильно оформить вставку переменных в этот запрос?

• Вопрос задан более трёх лет назад
• 19776 просмотров

Оценить 1 комментарий

• Facebook
• Вконтакте
• Twitter

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

Соответственно, тебе нужна библиотека, которая поддерживает плейсхолдеры. Хотя старушку mysql ext можно заставить, но для новичка лучше всего подойдет PDO.

Как соединяться с БД и вообще про PDO читай здесь: phpfaq.ru/pdo

А твоя ошибка вызвана опечаткой и неправильной обработкой ошибок.
Во-первых, у тебя в какой-то из запросов передается пустая строка — скорее всего, из-за опечатки
Во-вторых, если бы в РНР было включено нормальное отображение ошибок, то РНР тебе бы сам ругнулся на эту опечатку.
В-третьих, если бы ошибки mysql обрабатывались по-человечески, то РНР тоже указал бы на тот конкретный запрос, который оказался пустым. И ты бы не хватался вместо него за первый попавшийся.