:::::::::::::::::::::::::::::::::::::::::::
Главная страница
:::::::::::::::::::::::::::::::::::::::::::
Обучение
Лучшие ученики
Преподаватель
Регистрация
:::::::::::::::::::::::::::::::::::::::::::
Гостевая книга
:::::::::::::::::::::::::::::::::::::::::::
Документация
:::::::::::::::::::::::::::::::::::::::::::
О сайте
:::::::::::::::::::::::::::::::::::::::::::
Повышение квалификации
:::::::::::::::::::::::::::::::::::::::::::
Работа с MySQL: Подробнее Дмитрий Лебедев 2000-12-15 Полезные советы как избежать пустой траты системных ресурсов, как закрыть возможные дыры в защите. Самые популярные грабли. 1. Запросы на выборку данных (SELECT) 2. Запросы на вставку строки (INSERT) 3. Постраничный вывод 4. Функции 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), если это первая страница, либо ничего не выдаёт. Третья функция из тех же трёх параметров и адреса для ссылки делает навигационную строку. Ещё одна функция выдаёт число для нумерованного списка. Этого достаточно для нормальной работы с постраничным выводом данных. Посмотрим, что получается в коде программы: Это ВСЁ, что нужно для постраничного вывода! Больше напрягаться не надо! Одно только пояснение — в качестве параметра функции 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('написать письмо'),'адрес не указан') 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().
Статьи
21 ошибка программиста. Часть1
Подробнее

21 ошибка программиста. Часть2
Подробнее

21 ошибка программиста. Часть3
Подробнее

Вступление в PHP и MySQL.
Подробнее

Ловля ошибок в PHP.
Подробнее

Народная самодеятельность.
Подробнее

Оптимизация запросов в MySQL.
Подробнее

Пишем PHP код. Проба пера
Подробнее

Приемы безопасного програмиров-я
Подробнее

Работа с MySQL 1. Новостная лента
Подробнее

Работа с MySQL 2. Деревья
Подробнее

Работа с MySQL 3. Подробности
Подробнее

Обучающая система "Язык PHP", 2006 г.