Работа с базой данных MySQL средствами PHP

РАБОТА С БАЗОЙ ДАННЫХ MySQL СРЕДСТВАМИ РНР

Лекция. Подготовлена Прохоровым В.С.

1. СОЕДИНЕНИЕ РНР-СЦЕНАРИЕВ с таблицами MySQL

Рассмотрим наиболее часто используемых функций, позволяющих работать с базой данных MySQL средствами РНР.

При взаимодействии РНР и MySQL программа взаимодействует с СУБД посредством совокупности функций.

1.1 Соединение с сервером. Функция mysql_connect

Прежде чем работать с базой данных, необходимо установить с ней сетевое соединение, а также провести авторизацию пользователя. Для этого служит функция mysql_connect()

resource mysql_connect([string $server[,string $username[,string $password]]])

Эта функция устанавливает сетевое соединение с базой данных MySQL, расположенной на хосте $server (по умолчанию это localhost, т.е. текущий компьютер) и возвращает идентификатор открытого соединения. Вся дальнейшая работа ведется именно с этим идентификатором. Все другие функции, принимающие этот идентификатор (дескриптор) в качестве аргумента, будут однозначно определять выбранную базу данных. При регистрации указывается имя пользователя $username и пароль $password (по умолчанию имя пользователя, от которого запущен текущий процесс – при отладке скриптов: root, и пустой пароль):

$dblocation = "localhost"; //Имя сервера

$dbuser = "root"; //Имя пользователя

$dbpasswd = ""; //Пароль

//Осуществляем соединение с сервером базы данных

//Подавляем вывод ошибок символом @ перед вызовом функции

$dbcnx = @ mysql_connect($dblocation, $dbuser, $dbpasswd);

if (!$dbcnx) //Если дескриптор равен 0, соединение не установлено

{

//Выводим предупреждение

echo("<P>B настоящий момент сервер базы данных не доступен, поэтому корректное отображение страницы невозможно.</Р>");

exit ();

}

Переменные $dblocation, $dbuser и $dbpasswd хранят имя сервера, имя пользователя и пароль.

1.2 Разрыв соединения с сервером. Функция mysql_close

Соединение с MySQL – сервером будет автоматически закрыто по завершении работы сценария, либо же при вызове функции mysql_close

bool mysql_close ([resource $link_identifier])

Эта функция разрывает соединение с сервером MySQL, и возвращает true при успешном выполнении операции и false в противном случае. Функция принимает в качестве аргумента дескриптор соединения с базой данных, возвращаемый функцией mysql_connect.

Пример работы с этой функцией:

$dblocation = "localhost"; //Имя сервера

$dbuser = "root"; //Имя пользователя

$dbpasswd = ""; //Пароль

//Осуществляем соединение с сервером базы данных

//Подавляем вывод ошибок символом @ перед вызовом функции

$dbcnx = @ mysql_connect($dblocation, $dbuser, $dbpasswd);

if (!$dbcnx) //Если дескриптор равен 0, соединение не установлено

{

//Выводим предупреждение

exit ();

}

if (mysql_close($dbcnx)) //разрываем соединение

{

echo("Соединение с базой данных прекращено");

}

else

{

echo("He удалось завершить соединение");

1.3 Создание базы данных. Функция CREATE DATABASE

Команда — создание базы данных доступна только администратору сервера, и на большинстве хостингов ее нельзя выполнять:

CREATE DATABASE ИмяБазыДанных

Создает новую базу данных с именем имяБазыданных.

Пример работы с этой функцией:

//Создаем базу данных $dbname – это может делать только суперпользователь

//Если база данных уже существует, будет некритическая ошибка

@mysql_query('CREATE DATABASE $dbname');

Рекомендуется везде использовать апострофы ('SQL – команда') в качестве ограничителей строк, содержащих SQL – команды. Этим можно гарантировать, что никакая $ - переменная случайно не будет интерполирована (т.е. не заменится на свое значение), и увеличится безопасность скриптов.

Команда создания базы данных CREATE DATABASE доступна только суперпользователю, и на большинстве хостингов простому пользователю ее выполнить невозможно. Она доступна только администратору сервера.

Для экспериментов создадим базу данных testbase, выполнив SQL-запрос из командной строки. Для этого нужно войти в систему MySQL и ввести в командной строке MySQL:

mysql> create database testbase;

После этого следует набрать:

mysql>use testbase;

База данных создана:

1.4 Выбор базы данных. Функция mysql_select_db

До того как послать первый запрос серверу MySQL, необходимо указать, с какой базой данных мы собираемся работать. Для этого предназначена функция mysql_select_db:

bool mysql_select_db(string $database_name [,resource $link_identifier])

Она уведомляет PHP, что в дальнейших операциях с соединением $link_identifier будет использоваться база данных $database_name.

Использование этой функции эквивалентно вызову команды use в SQL-запросе, т. е. функция mysql_select_db выбирает базу данных для дальнейшей работы, и все последующие SQL-запросы применяются к выбранной базе данных. Функция принимает в качестве аргументов название выбираемой базы данных database_name и дескриптор соединения resource. Функция возвращает true при успешном выполнении операции и false — в противном случае:

//Код соединения с базой данных

if (! @mysql_select_db($dbname, $dbcnx))

{

//Выводим предупреждение

echo("<P> B настоящий момент база данных не доступна, поэтому корректное отображение страницы невозможно. </Р>");

exit(); }

1.5 Обработка ошибок

Если в процессе работы с MySQL возникают ошибки (например, в запросе не сбалансированы скобки или же не хватает параметров), то сообщение об ошибке и ее номер можно получить с помощью описанных далее двух функций.

Важно аккуратно и своевременно использовать эти функции, потому что иначе отладка сценариев может усложниться.

● Функция:

int mysql_errno ([int $link_identifier])

возвращает номер последней зарегистрированной ошибки. Идентификатор соединения $link_identifier можно не указывать, если за время работы сценария было установлено только одно соединение.

● Функция:

string mysql_error([int $link_identifier])

возвращает не номер, а строку, содержащую текст сообщения об ошибке. Ее удобно применять в отладочных целях. Обычно mysql_error используют вместе с конструкцией or die (), например:

@mysql_connect("localhost", "user", "password")

or die("Ошибка при подключении к базе данных: ".mysql_error());

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

В последних версиях РНР предупреждения в MySQL-функциях по умолчанию не регистрируются.

1.6 Автоматизация подключения к MySQL. Файл (config.php)

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

Код, ответственный за подключение к MySQL рекомендуется выделить в отдельный файл, а затем подключать с помощью функции include к нужным скриптам.

Имеет смысл помещать функции для соединения, выбора и создания базы данных в тот же файл (config.php), где объявлены переменные с именем сервера $dblocation, именем пользователя $dbuser, паролем $dbpasswd и именем базы данных $dbname:

Листинг config.php:

//config.php код файла, содержащего параметры соединения с сервером и выбора базы данных

//выводит сообщения об ошибках соединения в браузер

$dblocation = "localhost"; //Имя сервера

$dbname = "вставить имя базы" //Имя базы данных: создаваемой или уже существующей

$dbuser = "root"; //Имя пользователя базы данных

$dbpasswd = ""; //Пароль

//Осуществляем соединение с сервером базы данных

//Подавляем вывод ошибок символом @ перед вызовом функции

$dbcnx=@mysql_connect($dblocation,$dbuser,$dbpasswd);

if (!$dbcnx) //Если дескриптор равен 0, соединение с сервером базы данных не установлено

{

//Выводим предупреждение

echo("<p> В настоящее время сервер базы данных не доступен, поэтому корректное отображение страницы невозможно. </p>");

exit();

}

//Создаем базу данных $dbname – это может делать только суперпользователь

//Если база данных уже существует, будет некритическая ошибка

@mysql_query('CREATE DATABASE if not exists $dbname’);

or die("MySQL error: ".mysql_error());

//Код соединения с базой данной: осуществляем однозначный выбор только что созданной базы или уже существующей базы данных

//Подавляем вывод ошибок символом @ перед вызовом функции

if(!@mysql_select_db($dbname, $dbcnx)) //Если дескриптор равен 0, соединение с базой данных не установлено

{

//Выводим предупреждение

echo("<p> В настоящее время база данных не доступна, поэтому корректное отображение страницы невозможно. </p>");

exit();

}

//Небольшая вспомогательная функция, которая выводит сообщение

//об ошибке в случае ошибки запроса к базе данных

function puterror($message)

{

echo("<p> $message </p>");

exit();

}

2. ВЫПОЛНЕНИЕ ЗАПРОСОВ К БАЗЕ ДАННЫХ

2.1 Создание таблицы. Функция CREATE TABLE:

CREATE [IF NOT EXISTS] TABLE Имя Таблицы (ИмяПоля тип, ИмяПоля тип,)

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

Листинг test_11.php. Программа, создающая новую таблицу в базе данных:

<?php ## Создание новой таблицы в БД.

include "config.php";//Подключение к серверу и выбор базы данных

mysql_query('CREATE TABLE if not exists people

(

id INT AUTO_INCREMENT PRIMARY KEY,

name TEXT)');

or die("MySQL error: ".mysql_error());

Этот сценарий создает новую таблицу people с двумя полями. Первое поле имеет тип INT (целое) и имя id. Второе — тип TEXT (текстовая строка) и имя name.

Если таблица существует, сработает конструкция or die ().

Необязательная фраза if not exists, если она задана, говорит серверу MySQL, что он не должен генерировать сообщение об ошибке, если таблица с указанным именем уже существует в базе данных.

Необходимо сделать нужные изменения (изменить название базы данных на testbase) в файле config.php:

Таблица people создана:

Можно просмотреть перечень таблиц созданной базы данных c помощью оператора SHOW:

Можно отобразить информацию о столбцах всех таблиц c помощью оператора DESCRIBE:

Для просмотра данных, сохраненных в таблице, можно применить оператор SELEKT:

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

2.2 Вставка записей в таблицу. Функция INSERT

INSERT INTO ИмяТаблицы(ИмяПоля1 ИмяПоля2 ...) VALUES ('зн1', 'зн2',...)

Добавляет в таблицу ИмяТаблицы запись, у которой поля, обозначенные как ИмяПоля1 ИмяПоля2 ...установлены в значения соответственно зн№.

Те поля, которые в этой команде не перечислены, получают "неопределенные" значения.

Неопределенное значение (NULL) — это не пустая строка, а просто признак, который говорит MySQL, что у данного поля нет никакого значения.

Впрочем, если для неуказанного здесь поля при создании таблицы был задан NOT NULL, то поле получит значение по умолчанию (чаще всего 0 или пустая строка). Значения полей можно заключать и в обычные кавычки, но апострофы тут использовать удобнее; к тому же, так положено по стандарту SQL. При вставке в таблицу бинарных данных (или текстовых, содержащих апострофы и слэши) некоторые символы должны быть "защищены" обратными слэшами, а именно символы \, ' и символ с нулевым кодом (в РНР обозначается как "\х00" или chr(0)).

Существует альтернативный синтаксис для данной команды, специфичный для MySQL:

INSERT INTO ИмяТаблицы SET ИмяПоля1='зн1', ИмяПоля2='зн2', . . .

На практике он часто оказывается удобнее первого.

2.3 Удаление записей. Функция DELETE

DELETE FROM ИмяТаблицы WHERE выражение

Удаляет из таблицы ИмяТаблицы все записи, для которых выполнено выражение. Параметр выражение — это просто логическое выражение, составленное "почти" по правилам РНР. Вот показательный пример:

DELETE FROM topics WHERE forum_id=10 AND user != "moderator"

В выражении, помимо имен полей, констант и операторов, могут также встречаться простейшие "вычисляемые" части, например: (id < 10+11*234).

Вообще говоря, формат выражения един для всех команд запросов, которые мы встретим в дальнейшем. Например, он же используется и в операции SELECN, и в операции UPDATE.

2.3 Обновление записей. Функция UPDATE

UPDATE ИмяТаблицы SET (ИмяПоля1= 'зн1', ИмяПоля1— 'зн2', ...) WHERE выражение

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

2. Отправка запроса серверу для извлечения одной стоки из таблицы базы данных. Функция mysql_query

resource mysql_query (string query)

Эта функция применяется для отправки серверу SQL-запросов. Функция возвращает дескриптор запроса в случае успеха и false — в случае неудачного выполнения запроса.

В листинге показан код, с помощью которого извлекается одна строка из таблицы customers базы данных books:

include "config.php";//Подключение к серверу и выбор базы данныхх

$ath = mysql_query("select * from customers;");

if($ath)

{

$author = mysql_fetch_array($ath);

echo "<br>имя = ".$author['name']."<br>";

echo "адрес = ".$author['city']."<br>";

}

else

{

echo "<p><b>Error: ".mysql_error () . "</b></p>" ;

exit () ;

}

Результат выполнения запроса для вывода одной строки из таблицы:

2.. Вывод всех строк таблицы базы данных в виде ассоциативного массива. Функция mysql_fetch_array

array mysql_fetch_array (resource result)

Эта функция возвращает значения полей в виде ассоциативного массива. В качестве аргумента принимает дескриптор запроса, возвращаемый функцией mysql_query.

В листинге показано, как с помощью этой функции можно вывести все строки таблицы customers:базы данных books

include "config.php";//Подключение к серверу и выбор базы данных

$ath = mysql_query("select * from customers;");

if($ath)

{

//Определяем таблицу и заголовок

echo "<table border=1>";

echo"<tr>

<td>адрес</td>

</tr>";

//Так как запрос возвращает несколько строк, применяем цикл

while($author = mysql_fetch_array($ath))

{

echo "<tr>

<td>".$author['name']." </td>

<td>".$author['city']." &nbsp </td>

</tr>";

}

echo "</table>";

}

else

{

echo "<p><b>Error: " .mysql_error () . "</b><p>";

exit () ;

}

Результат выполнения запроса для вывода всех строк из таблицы:

2.. Доступ к отдельному полю записи. Функция mysql_result

mixed mysql_result (resource result, int row)

С помощью этой функции можно получить доступ к отдельному полю записи. Допустим, нам нужно вывести имя автора, которое первым найдется в базе данных. Сделать это можно следующим образом:

include "config.php";//Подключение к серверу и выбор базы данных

$ath = mysql_query("select name from customers;");

if($ath)

{

echo mysql_result($ath,0,'name');

}

else

{

echo "<p><b>Error: " .mysql_error () . "</b><p>";

exit () ;

}

2.. Возвращение поля записи в виде объекта. Функция mysql_fetch_object

object mysql_fetch_object (resource result)

Эта функция возвращает поля записи данных в виде объекта.

В листинге приведен пример, в котором с помощью этой функции из таблицы customers выводятся имя, и адрес авторов:

include "config.php";//Подключение к серверу и выбор базы данных

$ath = mysql_query("select * from customers;");

if($ath)

{

while($row = mysql_fetch_object($ath))

{

echo "<p>имя: ".$row->name."</p>";

echo "<p>адрес: ".$row-> city."</p>";

}

else

{

echo "<p><b>Error: ".mysql_error () . "</b><p>";

exit();

}

Результат выполнения скрипта:

2.. Возвращение массива, в котором содержится значение поля. Функция mysql_fetch_row

array mysql_fetch_row (resource result)

В отличие от функции mysql_fetch_object, эта функция возвращает не объект, а массив, в котором содержатся значения полей:

include "config.php";//Подключение к серверу и выбор базы данных

$ath = mysql_query("select * from customers;");

if($ath)

{

while($row = mysql_fetch_row($ath))

{

echo "<p>имя: ".$row[3]."</p>";

echo "<p>адрес: ".$row[2]."</p>";

}

else

{

echo "<p><b>Error: " .mysql_error () . "</b><p>";

exit ();

}

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

2.. Пример комплексного использования информационных функций

Листинг info_1.php

<?php ## Получение информации о таблице.

include "config.php";//Подключение к серверу и выбор базы данных

// Получаем все данные таблицы.

$result = mysql_query('SELECT * FROM people');

// Запрашиваем идентификатор данных о полях таблицы.

$fields = mysql_num_fields ($result);

// Узнаем число записей в таблице.

$rows = mysql_num_rows($result);

// Получаем имя таблицы (правда, мы его и так знаем, но все же...)

$table = mysql_field_table($result,0);

echo "Таблица '$table' содержит $fields колонок и $rows cтpoк<BR>"

echo "Таблица содержит следующие поля:<ВR>";

// "Проходимся" по всем полям и выводим информацию о них.

for ($i=0; $i<$fields; $i++)

{

$type = mysql_field_type($result, $i);

$name = mysql_field_name($result, $i);

$len = mysql_field_len($result, $i);

$flags.= mysql_field_flags($result, $i) ;

echo "$name $type($len) $flags<BR>\n";

}

3. MySQL И ПРОБЛЕМЫ БЕЗОПАСНОСТИ

Запросы, отправляемые серверу MySQL, представляют собой обыкновенные строки РНР:

mysql_query("INSERT INTO table SET name='$name'");

В $name может храниться строка, содержащая апострофы.

Рассмотрим, какой запрос придет серверу MySQL, если $name равно "cat's":

INSERT INTO table SET name='cat's'

Эта команда синтаксически некорректна и породит ошибку во время выполнения.

Но может быть и хуже.

Рассмотрим такой запрос:

mysql_query("DELETE FROM table WHERE name='$name'");

Если параметр $name приходит из формы, и злоумышленник указал в нем следующую строку: "!' or 1=1 or '!", то после подстановки получится такой запрос к базе данных:

DELETE FROM table- WHERE name=' !' OR 1=1 OR ' !'

Этот запрос удалит все записи из таблицы table, потому что выражение SQL 1=1 всегда истинно.

Рассмотрим два способы защиты от подобных ошибок или действий злоумышленника:

● Экранирование спецсимволов.

● Шаблоны запросов и placeholders.

3.1 Экранирование спецсимволов

Прежде чем передавать значения переменных формы в SQL-запросы, необходимо специальным образом экранировать в них некоторые символы (в частности, апостроф), например, поставить перед ними обратный слэш. Для вставки предназначена функция:

mysql_escape_string()

string mysql_escape_string(string $str)

Функция похожа на другую функцию addslashes(), однако она добавляет слэши перед более полным набором специальных символов. Практика показывает, что для текстовых данных можно применять и функцию addslashes() вместо mysql_escape_string(). Во многих скриптах так и делается.

По стандарту MySQL экранированию подвергаются символы, которые в РНР записываются так: "\х00", "\n", "\г", "\\", ""', "" и "\х1А".

В это число входит символ с нулевым ASCII-кодом, а поэтому mysql_escape_string() допустимо применять не только для текстовых, но также и для бинарных данных. Можно, например, считать в переменную GIF-изображение (функция file_get_contents ()), а затем вставить его в базу данных, предварительно проэкранировав все спецсимволы. При извлечении картинка окажется в том же виде, в котором она была изначально.

Экранирование символов это лишь способ записи корректных SQL-выражений, не более того. С данными ничего не происходит, и они хранятся в базе без дополнительных слэшей — так, как выглядели изначально, еще до экранирования.

С использованием mysql_escape_string()код предыдущего запроса выглядит так:

mysql_query(

"DELETE FROM table WHERE name='".mysql_escape_string($name)."'" );

Это длинно, неуклюже и некрасиво.

3.2 Шаблоны запросов и placeholders

Рассмотрим другое решение.

Вместо явного экранирования и вставки переменных в запрос на их место помещают специальные маркеры (placeholders, "хранители места"), обычно выглядящие как ?.

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

С использованием гипотетической функции mysql_qwo, код которой будет представлен ниже, предыдущий запрос может быть переписан так:

mysql_qw ('DELETE FROM table WHERE name=?', $name);

Запрос стал короче и лучше защищен: теперь мы уже при написании кода не сможем случайно пропустить вызов функции mysql_escape_string() и, таким образом, попасться на уловку хакера. Все преобразования происходят автоматически, внутри функции.

В листинге lib_mysql_qw.php содержится простейшая реализация функции mysql_qw() (qw — от англ. query wrapper, "обертка для запроса").

Имеется также библиотека lib/Placeholder.php, обеспечивающая значительно более мощную поддержку языка placeholders: http://dklab.ru/chicken/30.html.

В большинстве ситуаций возможностей, предоставляемых функцией mysql_qw (), оказывается достаточно.

Листинг lib_mysql_qw.php

<?php ## Простейшая функция для работы с placeholders.

// result-set, mysql_qw ($connection_id, $query, $argl, $arg2 ...).

// - или -

// result-set mysql_qw($query, $argl, $arg2, ...)

// Функция выполняет запрос к MySQL через соединение, заданное как

// $connection_id (если не указано, то через последнее открытое).

// Параметр $query может содержать подстановочные знаки ?,

// вместо которых будут подставлены соответствующие значения

// аргументов $arg1, $arg2 и т. д. (по порядку), экранированные и

// заключенные в апострофы.

function mysql_qw()

{

// Получаем все аргументы функции.

$args = func_get_args();

// Если первый параметр имеет тип "ресурс", то это ID-соединения.

$соnn = null;

if (is_resource($args[0])) $conn = array_shift($args);

// Формируем запрос по шаблону.

$query = call_user_func_array("mysql_make_qw", $args);

// Вызываем SQL-функцию.

return $conn!==null ? mysql_query($query, $conn): mysql_query($query);

}

// string mysql_make_qw($query, $argl, $arg2,...)

// Данная функция формирует SQL-запрос по шаблону $query,

// содержащему placeholders.

function mysql_make_qw()

{

$args = func_get_args();

// Получаем в $tmp1 ССЫЛКУ на шаблон запроса.

$tmp1 =& $args[0];

$tmp1 - str_replace("%", "%%", $tmp1);

$tmp1 = str_replace("?", "%s", $tmp1);

// После этого $args[0] также окажется измененным.

// Теперь экранируем все аргументы, кроме первого.

foreach ($args as $i=>$v)

{

if (!$i) continue; // это шаблон

if (is_int($v)) continue; // целые числа не нужно экранировать

$args[$i] = "'".mysql_escape_string($v)."'";

}

//На всякий случай заполняем 20 последних аргументов недопустимыми

// значениями, чтобы в случае, если число "?" превышает количество

// параметров, выдавалась ошибка SQL-запроса (поможет при отладке).

for ($i=$c=count($args)-1; $i<$c+20; $i++)

$args[$i+1] = "UNKNOWN_PLACEHOLDER_$i";

// Формируем SQL-запрос.

return call_user_func_array("sprintf", $args);

}

Если убрать поясняющие записи, то размер файла lib_mysql_qw.php уменьшится почти в три раза:

<?php ## Простейшая функция для работы с placeholders.

function mysql_qw()

{

$args = func_get_args();

$соnn = null;

if (is_resource($args[0])) $conn = array_shift($args);

$query = call_user_func_array("mysql_make_qw", $args);

return $conn!==null ? mysql_query($query, $conn): mysql_query($query);

}

function mysql_make_qw()

{

$args = func_get_args();

$tmp1 =& $args[0];

$tmp1 - str_replace("%", "%%", $tmp1);

$tmp1 = str_replace("?", "%s", $tmp1);

foreach ($args as $i=>$v)

{

if (!$i) continue;

if (is_int($v)) continue;

$args[$i] = "'".mysql_escape_string($v)."'";

}

for ($i=$c=count($args)-1; $i<$c+20; $i++)

$args[$i+1] = "UNKNOWN_PLACEHOLDER_$i";

return call_user_func_array("sprintf", $args);

}

Функция sprintf() воспринимает символ % как управляющий. Чтобы отменить его специальное действие, необходимо его удвоить, что и делается в функции. Затем ? заменяется на %s, для sprintf() это означает "взять очередной строковый аргумент".

Для удобства тестирования этого кода главная функция разбита на две, выделен код замены подстановочных знаков в функцию mysql_make_qw().

В листинге test_qw.php приведен пример того, как будут выглядеть SQL-запросы после подстановки placeholders.

Листинг test_qw.php

<?php

require_once "lib_mysql_qw.php";

require_once "mysql_connect.php";

// Представим, что мы - хакеры...

$name = "' OR '1";

// Допустимый запрос.

echo mysql_make_qw('DELETE FROM people WHERE name=?', $name)."<br>";

// Недопустимый запрос.

echo mysql_make_qw('DELETE FROM people WHERE name=? OR ?', $name)."<br>";

// Вот как выглядит выполнение запроса.

mysql_qw('DELETE FROM people WHERE name=? OR ?', $name)

or die(mysql_error());

В результате работы скрипта будет сгенерирована следующая страница:

DELETE FROM people WHERE name='\' OR \'1'

DELETE FROM people WHERE name=' \ ' OR \ ' 1' OR id=UNKNOWN_PLACEHOLDER_l

Unknown column 'UNKNOWN_PLACEHOLDER_1' in 'where clause1

Перед апострофами в данных появились слэши, a placeholder, которому "не хватило" аргументов функции, оказался замененным на строчку UNKNOWN_PLACEHOLDER_l.

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

3.3 Пример применения СУБД MySQL

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

● добавлять новую запись; при этом она помечается текущей датой и помещается в таблицу базы данных;

● удалять некоторую запись по ее идентификатору.

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

Листинг guestbook.php

<? php ## Простейшая гостевая книга.

require_once "mysql_connect.php";

require_once "lib_mysql_qw.php";

// Имя таблицы.

define("TBLNAME", "guestbook");

// Создаем таблицу, если она еще не существует.

mysql_qw ('CREATE TABLE IF NOT EXISTS '.TBLNAME.' (

id INT AUTO_INCREMENT PRIMARY KEY,

stamp TIMESTAMP,

name VARCHAR(60),

text TEXT

)

or die(mysql_error()) ;

// Обрабатываем кнопки и действия.

if (@$_REQUEST['doAdd'])

{

// Получаем данные из формы.

$element = $_REQUEST['element'];

// Удаляем слэши в данных, которые РНР вставил в режиме

// magic_quotes_gpc (если он включен).

if (ini_get("magic_quotes_gpc"))

$element = array_map('stripslashes', $element);

// Вставляем запись.

mysql_qw(

'INSERT INTO '.TBLNAME. 'SET name=?, text"?',

$element['name'], $element['text']

)

or die(mysql_error());

// Выполняем "самопереадресацию", чтобы при нажатии кнопки

// "Обновить" в браузере сообщение не добавлялось снова и снова.

Header ("Location: {$_SERVER['SCRIPT_NAME']}?".time());

exit ();

}

// Удаление сообщения с указанным ID.

if ($delid = @$_REQUEST['delete'])

{

mysql_qw ('DELETE FROM '.TBLNAME.' WHERE id=?', $delid)

or die(mysql_error());

}

// Выбираем все записи из таблицы, начиная с самой новой.

$result = mysql_qw('

-- Функция MySQL UNIX_TIMESTAMP() конвертирует, timestamp

-- из формата MySQL в число секунд с начала эпохи Unix.

SELECT *, UNIX_TIMESTAMP(stamp) AS stamp

FROM ' . TBLNAME. '

ORDER BY stamp DESC

or die(mysql_error());

for ($book=array();

$row=mysql_fetch_array($result);

$book[]=$row);

<! -- Далее идет шаблон книги. -->

<table>

</tr>

<td>Teкст сообщения:</td>

</tr>

<tr>

<td><input type="sub>mit" name="doAdd" value="Добавить"</td>

</table>

</form>

<hr>

foreach($book as $element)

{

<b>

<? =date ("d.m.Y", $element ['stamp'])?>

<? =htmlspecialchars ($element ['name'])?>

</b>

написал:

<a href="<?=$_SERVER['SCRIPT_NAME']?>?delete=<?=$element['id']

[удалить]</a>

<br>

<?=n12br(htmlspecialchars($element['text']))

</blockquote>

<hr>

}

Этот скрипт использует удобные на практике приемы.

● Вначале включают код mysql_connect.php для подключения к базе данных, а также библиотеку lib_mysql_qw.php для выполнения "защищенных" запросов.

Дальше ИСПОЛЬЗУЕТСЯ ТОЛЬКО ФУНКЦИЯ mysql_qw(), и не применяется вызов функции mysql_query() напрямую.

● Создается константа, хранящая имя таблицы гостевой книги в базе данных. Использование константы вместо явного указания имени позволяет в дальнейшем легко сменить имя таблицы (если это понадобится).

● Создается таблица guestbook, имеющая 4 поля (столбца):

○ Автоинкрементное поле id, как обычно, служит для идентификации записей.

○ Поле stamp типа timestamp хранит время изменения данной записи. Тип timestamp удобен тем, что значение stamp изменяется сервером MySQL автоматически при вставке или модификации записи.

● Благодаря фразе IF NOT EXISTS MySQL создаст таблицу только при первом запуске скрипта, и ничего не будет делать при последующих запусках.

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

● Режим magic_quotes_gpc, устанавливаемый в файле php.ini, заставляет РНР вставлять слэши перед данными, пришедшими из формы. Так разработчики РНР попытались обезопасить программистов, использующих СУБД от распространенной ошибки с апострофами.

Так как мы обрабатываем апострофы самостоятельно (функция mysql_qw()), нам нужно вернуть данные в исходный вид, т. е. убрать из них все лишние слэши.

Можно подумать, что идея с magic_quotes_gpc хороша, и задаться вопросом: а зачем же вообще нужна функция mysql_qw(), если есть magic_quotes_gpc? Ответ на этот вопрос: данные, помещаемые в базу, могут прийти не только из формы, но и из других источников (a magic_quotes_gpc обрабатывает лишь данные формы).

● Выдавая заголовок Location, мы обеспечиваем так называемую самопереадресацию. Зачем она нужна? Попробуйте убрать вызов Header() (и идущий следом exit()), затем добавить в гостевую книгу запись и тут же нажать кнопку «Обновить» в браузере. Появится запрос: хотите ли вы послать данные формы повторно или нет. Если вы ответите «Да», в книгу добавится еще одна запись, идентичная первой. Если же ответите «Нет», то будет показано старое состояние гостевой книги, без только что добавленной записи. Самопереадресация обеспечивает корректность работы кнопки «Обновить», а добавляемое в query_string текущее время гарантирует, что браузер не станет кэшировать страницу.

● Интересна SQL-команда

SELECT *, UNIX_TIMESTAMP(stamp) AS stamp

Тип данных TIMESTAMP хранит информацию о времени в следующем представлении: 20051222000307. Первые 4 цифры определяют год, следующие две — месяц, и т. д. В то же время, для функции PHP date()нужен Unix timestamp-формат — число секунд, прошедших с 1 января 1970 года. Чтобы преобразовать первое представление во второе, используется функция UNIX_TIMESTAMP(), встроенная в MySQL. Суффикс " AS stamp " позволяет добавить вычисленное поле под именем stamp к остальным полям, которые были извлечены звездочкой (*).

В итоговый набор данных поле stamp должно бы было добавиться в конец списка полей, и результат должен бы получиться из 5 колонок (id, stamp, name, text и еще другая stamp, полученная при помощи " AS stamp " — не важно, что она имеет то же имя, что и вторая). Однако использование функции mysql_fetch_assoc() "гасит" первое поле stamp и заменяет его значением последнего.

Таким образом, в итоге переменная $row равна массиву из четырех элементов: (id, stamp, name, text), причем stamp идет в формате Unix timestamp, что и требовалось.

● При выводе данных в браузер их в обязательном порядке обрабатывают функцией htmlspecialchars(), чтобы злоумышленник не смог вставить в сообщение теги и "разрушить" структуру страницы. Обратите внимание, что данные хранятся в БД в исходном виде, а их обработка производится уже в самом конце, непосредственно перед выводом. Такая практика позволяет, например, легко написать скрипт редактирования записей в гостевой книге, или же изменить ее дизайн (например, добавить