Работа с MySQL: Подробнее

:

Работа с MySQL: Подробнее

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

1. Запросы на выборку данных (SELECT)

  • Во избежание путаницы полей (если встречаются поля с одинаковыми названиями) используйте в запросах оператор AS: "SELECT table1.id as id1, table2.id as id2". Это поможет избежать ошибок в запросе (например, если не указана таблица, а поле с таким названием есть в нескольких запрашиваемых таблицах, mysql выдаёт ошибку), а так же вы избежите недоразумений при работе с полученными данными (echo $row["id1"] писать гораздо проще, чем $row[$x]).
  • Данные типа DATE, TIME, DATETIME и TIMESTAMP можно форматировать с помощью функции date_format (см. руководство по mysql). Используйте его, и не форматируйте данные через php - это не просто "самодеятельность", а ещё и растрата системных ресурсов.
  • По возможности минимально используйте LEFT JOIN для объединения таблиц. Это весьма трудоёмкая операция для базы данных.
  • Там, где можно, используйте идентификаторы - выборка данных при указании ключевого поля происходит быстрее, чем при указании обычного.
  • Вместо "WHERE id=1 OR id=3 OR id=232" можно использовать встроенную функцию IN: "WHERE id IN (1,3,232)".
  • Если нужен текстовый поиск, осторожней со знаком "%". Во всяком случае, запросы типа somefield LIKE '%a%' лучше не делать - опять же слишком трудоёмкая операция. По крайней мере, надо фильтровать слова и отрезать те, которые короче 3 символов.
  • Используйте минимум необходимых полей в запросе. "SELECT * FROM sometable" выполняется медленнее, чем "SELECT id FROM sometable", тем более если в таблице много данных. Для подсчёта количества строк в таблице вообще (или подпадающих под некоторое условие) достаточно одного поля.
  • Разбивайте данные на страницы, используя оператор LIMIT. Это экономит время выполнения запроса и уменьшает объем страницы, которую получает пользователь.

Даже если вам не грозит "падение" от наплыва посетителей, лучше взять себе в привычку, чтобы потом не было проблем с адаптацией к новым задачам. Теперь о безопасности работы.

  • Старайтесь не допускать внесения в базу данных символа одинарной кавычки ("'"), поскольку это служебный символ запросов БД. Перед внесением в базу поле можно обработать функцией str_replace: $somefield = str_replace("'", "'", $somefield);

К тому же это лишний барьер на пути взломщиков вашего сайта. Пример "взлома" простой:

mysql_query("UPDATE users SET password=PASSWORD('$passwd') WHERE login='$login'");

Если кавычку не обработать на входе, злоумышленник может в качестве логина сунуть строку "vasya_pupkin' OR login LIKE '%". В базу данных залетит запрос: mysql_query("UPDATE users SET password=PASSWORD('$passwd') WHERE login='vasya_pupkin' OR login LIKE '%'"); То есть все пароли будут одинаковые. Это только один пример. Итак,

  • Обрабатывайте данные, получаемые из адресной строки или из формы, и приводите их к нужному типу во избежание ошибок и "взломов" сайта. (ещё пример: если требуется идентификатор, то есть целое число, надо обработать его с помощью intval: $id = intval($id)).

2. Запросы на вставку строки (INSERT)

  • Поле идентификатора вставлять не нужно. На это есть свойство поля AUTO_INCREMENT.

Забавно читать, как в форуме пишут:

- Как мне быть с генератором случайных чисел?! неправильно работает!
- А зачем тебе?
- Да в базе id использовать...
В общем, не надо самодеятельности.
  • Если в поле формата DATE, TIME, DATETIME или TIMESTAMP надо вставить текущее время, используйте встроенную в mysql функцию NOW: "INSERT INTO vote (ip, date) VALUES ($REMOTE_ADDR, NOW())"
  • Хранимые в базе пароли лучше прикрыть функцией php md5: "INSERT INTO user (login, pass) VALUES ('$login', ". md5($pass). ")" "SELECT * FROM user WHERE login='$login' AND pass=". md5($pass)

Советы, кажется, уже исчерпаны. Напоследок. С недавних пор я стал думать, что при написании скриптов, работающих с БД, надо ориентироваться не только на глупого и шаловливого посетителя, но и на криворукого администратора. Даже если мы внимательно будем следить за текстом, который вставляем в текстовое поле (одинарные кавычки не писать, делать их автозамену в Word-е, белое не носить), вероятность попадания служебных символов в запрос ненулевая.

3. Постраничный вывод

Регулярно в форуме задают один и тот же вопрос: как сделать постраничный вывод. И каждый раз человеку отвечают: "Легко! m строк, начиная с n-ной: Select запрос Limit $n,$m". На самом деле не так всё просто.

Я уже писал про синтаксис параметра LIMIT, однако, без толку. Для полноценного постраничного вывода строк из базы требуется большее. Требуется

  • Обработать номер страницы (в том числе проверить, не больше ли он общего количества страниц)
  • Нарисовать навигационную строку (чтобы не просто "вперед-назад", а с ссылками на несколько соседних страниц)

Тут-то и начинаются главные проблемы.

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

Первая функция — для внутреннего пользования двумя следующими. Берёт номер страницы, общее количество строк и количество строк на странице и выдаёт номер страницы, уже проверенный. Вторая берёт то же самое, проверяет номер страницы и выдаёт парамерт LIMIT либо полный (LIMIT n,m), либо краткий (LIMIT m), если это первая страница, либо ничего не выдаёт. Третья функция из тех же трёх параметров и адреса для ссылки делает навигационную строку. Ещё одна функция выдаёт число для нумерованного списка.

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

<?php
// кол-во строк в страницах
$in_page 10;

// получаем количество строк
$amount = @mysql_result(mysql_query("SELECT count(id) as goods_total FROM goods"),0);

// рисуем навигационную строку и пишем начало таблицы
print("<div align=center>". <b>draw_bar($page$amount$in_page
"goods.php?page=")</b>. "</div>\n<table>");

// формируем запрос к базе
$goods_result mysql_query("SELECT id, name, description, price FROM goods 
ORDER BY name, price "
. <b>get_limit($page$amount$in_page)</b>);

// получаем номер для нумерованного списка
$count = <b>get_count_limit($page$amount$in_page)</b>;

// выводим строки
while ($good_row mysql_fetch_array($goods_result)) {
$count++;
print (
"<tr");

  // фон каждой второй строки — серым цветом
if ($count/2==intval($count/2))
print (
" bgcolor=#e1e1e1");
print (
"><td align=right>$count.</td><td>${good_row[name]}
<br>
${good_row[description]}</td><td align=right>${good_row[price]}</td></tr>\n");
};

// конец таблицы и нижняя навигационная строка
print("</table><div align=center>". <b>draw_bar($page$amount
$in_page"goods.php?page=")</b>. "</div>\n");

Это ВСЁ, что нужно для постраничного вывода! Больше напрягаться не надо!

Одно только пояснение — в качестве параметра функции draw_bar указывается адрес этого скрипта со всеми параметрами так, чтобы он туда только дописывал номер страницы. Если сложная выборка, надо будет ручками формировать этот адрес (всё-таки упрощение жизни вышло относительное: упрощаем одно — усложняем другое).

Навигационная панель сделана в виде номеров страниц (" 1 | 2 | 3 "). Но привести к виду "0-10 | 11-20 | 21-30" не проблема.

4. Функции mySQL обработки данных

Должен признаться, много времени на изучение руководства по MySQL я не уделял, а брал его только когда было совсем необходимо что-то узнать. Как выяснилось, я многое потерял — вещи, над которыми иногда задумывался, оказывается, уже в базах данных MySQL реализованы.

Посмотрев в перечень математических функций, я несколько переделал подсчёт данных в своей игре. Данные вынимались из базы запросом, проводились вычисления, затем возвращались обратно. Теперь количество запросов для этой операции сведено к одному — отправляется сразу UPDATE-запрос, внутри которого указываются все вычисления и сопутствующие данные.

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

Функции условий

IFNULL(x,y) — если x не NULL, тогда выдаёт x, иначе — y.

NULLIF(x,y) — если x и y равны, выдаёт NULL, если не равны — x.

IF(x,y,z) — если x = true (вернее, если x не равен 0 и не NULL), выдаёт y, если нет — z.

К примеру, в форуме хранится информация о пользователях и есть возможность не показывать другим пользователям свой Email. Делается поле show_email, в котором лежит 0, если пользователь не хочет показывать адрес, и 1, если разрешает.

SELECT ..., IF (show_email,CONCAT('<a href=mailto:',email,'>написать письмо</a>'),'адрес не указан') AS email, ...

Математические функции

MOD(N,M) или "%" — остаток от деления N на M.

FLOOR и CEILING — округление до целого вниз и вверх.

ROUND — округление до целого или до определенной десятичной дроби.

LEAST (X,Y,...) и GREATEST(X,Y,...) — минимальное и максимальное числа из указанных.

Не упоминаю стандартные математические функции взятия модуля, знака, работы с углами.

Строковые функции

CONV(N,система_из,система_в) — конвертация числа из одной системы исчисления в другую: select CONV("ff",16,10); => 255. Кстати, конвертировать можно не только в стандартных системах (2,8,10,16), но и в любых других от 2 до 36 — насколько хватает букв латинского алфавита.

CONCAT(X,Y,...) — объединение строк и чисел в одну строку (пример приведён выше).

CONCAT_WS(разделитель,X,Y,...) — аналог функции implode.

LENGTH(строка) — strlen.

LOCATE(подстрока, строка) — strpos.

SUBSTRING(строка, отступ, длина) — substr.

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

REPLACE (строка, X, Y) — заменяет в строке X на Y (не перепутайте порядок с порядком параметров в str_replace).

Дата и время

Функций много, отмечу только некоторые самые важные: неправда, что MySQL считает дни недели только с воскресенья, как принято в Америке. Нужно использовать не функцию DAYOFWEEK, а WEEKDAY, тогда понедельнику будет соответствовать номер 0, вторнику — 1, воскресенью — 6.

Для сложного форматирования даты (например, для вывода даты в виде 18.08.01), есть функции DATE_FORMAT (для даты и времени) и TIME_FORMAT (только для времени). Работа с этими функциями удобнее, чем использование своих собственных (потому что это средство стандартное и универсальное, чего в самопальном приспособлении добиться очень сложно), а так же быстрее (используются встроенные функции mysql-сервера, которые уже сидят в памяти, вместо компиляции при каждом запуске скрипта собственного кода).

Юниксовский timestamp MySQL тоже поддерживает — переводы в него и из него через функции UNIX_TIMESTAMP и FROM_UNIXTIME:

UNIX_TIMESTAMP([дата-время]) — выдаёт дату в юниксовом формате (если аргумент пропущен — текущую дату).

FROM_UNIXTIME(дата [, формат]) — выдаёт дату в обычном формате (во втором аргументе может быть указан формат по правилам как в DATE_FORMAT).

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

Остальные функции

LAST_INSERT_ID() — как и mysql_insert_id(), выдаёт последний идентификатор, который сгенерировала база данных по запросу с данного соединения.

MD5(строка) — поскольку говорят, что зашифрованный функцией PASSWORD() пароль легко расшифровать, я храню хэш md5 от пароля.

FORMAT(X, D) — форматировать число X в виде "#,###,###.##", округлённое до D знаков после запятой. Подумал, что неплохо бы в моей игре сделать форматированные для удобного чтения числа, глянул в руководство, вот оно. Всё уже написано.

Ещё две функции, про которые я забыл, когда писал про оптимизацию работы логов.

INET_NTOA(число) — аналог long2ip().

INET_ATON(ip-адрес) — аналог ip2long().

А я только начал думать, как отделять статистику по ip-адресам от всех других выборок в анализаторе логов. Оказывается, всё проще, чем кажется.


For comment register here
   Unknown 2001-01-21 14:34
> Данные типа DATE, TIME, DATETIME и TIMESTAMP можно форматировать с помощью функции
> date_format (см. руководство по mysql). Используйте его, и не форматируйте данные
> через php - это не просто "самодеятельность", а ещё и растрата системных ресурсов.

Можно подумать, что MySQL, форматируя дата не тратит те же системные ресурсы... хотя если сервер СУБД на другой машине, то тратятся конечно другие. Но все равно, те же яйца.





   2001-03-19 16:45
> $somefield = str_replace("'", "'", $somefield). Хммм... А может быть правильно $somefield = str_replace("'", "", $somefield)?

А можно просто пресечь все(кроме a-z 0-9 - кто-нибудь серьезно пользуется логинами типа ##kolya или $%^vasya?) Из полученной строки исключаются все ключевые слова SQL.
Если это имя пользователя, и при регистрации он захотел себе странноватое имя, напоминающее SQL запрос, то вываливается сообщение типа "Это имя уже занято..." :)

Проще надо быть, а то живете почти что на физическом уровне OSI, про простейшие вещи забываете. И про методологию проектирования БД почти ничего нет. Так в основном, примитивные вещи. Скажете - а зачем это "им"? "Нам" это было бы полезно... Местами...
После нас эта "БЭДЭ" достанется другим. Очень не хочется, чтобы кто-нибудь плевался из-за того, что я когда-то считал себя Программистом.



   Unknown 2001-04-30 16:40
коментарий со ссылки выше
Php doesn't strip the slashes when info comes out of the database. The difference here is that php4 automatically adds slashes to incomming variables (from a post/get) and php3 didn't. So if you're adding slashes manually, suddenly you have double. Either drop your second addslashes() or add a stripslashes to the beginning to all the variables that come in.

Истинная правда, и про это нельзя забывать


   Unknown 2001-04-30 16:54
Навигационная строка может использоваться два раза - если списка на странице два (хотя это, конечно, редкий изврат :). Просто у меня include и require для вывода данных запрещены. Потому что шаблонами пользуюсь. Там делается

$page->assign(array("limit" => get_limit(...), "nav_bar" => draw_bar(...)));

>страница 5 из 46
Кому надо, пусть свою функцию напишет - это ж для программистов сайт! Что им, сложно? :)



   Unknown 2001-05-06 12:03
> if ($count/2==intval($count/2))

Замени на $count & 1. После такого остально читать не хочется.


   Unknown 2001-05-06 12:05
> $amount = @mysql_result(mysql_query("SELECT count(id) as goods_total FROM goods"),0);

Э... а это что? :((
list ($amount) = mysql_fetch_row (...); не лучше?










   2001-07-30 08:35
У нас есть сайт. Написан на php. Разбираемся с ним и учимся.

Проблема следующая. На сайте сейчас все перемешано. Вся информация
берется из базы. Хотелось бы всю информацию упорядочить по темам. Не совсем понятно как это сделать с помощью PHP. Как сформировать запрос к базе так, чтобы на страничку выводилась только нужная инфа? Хотелось бы базу не трогать, а переписать только сами запросы. Т.е. на страничке уже должны быть отсортированные данные.

И еще. На серваке хранится файл без расширения весом 280К. Так как
ничего похожего на базу больше нет, то есть предположение, что это и есть
база. Вобще обязательно ли база MySQL должна иметь расширение? И какое?








   Unknown 2001-09-28 11:43
2Lina: смотрим доку. Видим:
--------------------------------
resource mysql_query (string query [, resource link_identifier])
mysql_query() sends a query to the currently active database on the server that's associated with the specified link identifier. If link_identifier isn't specified, the last opened link is assumed. If no link is open, the function tries to establish a link as if mysql_connect() was called with no arguments, and use it.
--------------------------

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








   2003-05-26 18:48
> if ($count/2==intval($count/2))
ИМХО не лучший вариант. Не красиво!!!
Я делаю так:
$bgcolor=array("#eeeeee", "#fafafa");
for ($i=0, $y=count($post); $i<$y; $i++) {
print "<tr bgcolor=".$bgcolor[($i % 2)]."><td></td></tr>";
}//for
Эту идею и здесь прикрутить нет проблемммм...
А ваще, и дальше всT в таком же духе... слишком много кода... поэтому особо не вникал... функции какие-то... зафиг они нужны?.. но может одна для навигации и всT... Мой собственный код работает у меня без проблем при том, что он раз 5-ть короче... и в нTм отсутствуют баги про которые говорили выше...