Учебное пособие: Работа с базой данных MySQL средствами PHP
{
echo "<tr>
<td>".$author['name']." </td>
<td>".$author['city']."   </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);
?>
<! -- Далее идет шаблон книги. -->
<form action="" method="post">
<table>
<tr valign="top">
<td>Baшe имя:</td>
<td><input type ="text" name="element [name] "></td>
</tr>
<tr valign="top">
<td>Teкст сообщения:</td>
<td><textarea name="element[text]" cols="60" rows="5"></textarea></td>
</tr>
<tr>
<td> </td>
<td><input type="submit" 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>
<blockquote>
<?=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(), чтобы злоумышленник не смог вставить в сообщение теги и "разрушить" структуру страницы. Обратите внимание, что данные хранятся в БД в исходном виде, а их обработка производится уже в самом конце, непосредственно перед выводом. Такая практика позволяет, например, легко написать скрипт редактирования записей в гостевой книге, или же изменить ее дизайн (например, добавить
