Из записных книжек: Пишем генератор паролей на SQL (PSQL) Firebird 2.5

Встроенный язык PSQL (procedure SQL) в СУБД Firebird последних версий достаточно богат и выразителен, чтобы без затруднений делать вполне нетривиальные вещи. Например, написать процедуру генерации не слишком сложных паролей. Предваряя вопрос «зачем?»: пусть имеется множество проектов, написанных на разных языках, но работающих с одной базой данных, и во всех нужно реализовать такую функцию, как генерация пароля. Не хочется многократно копировать одинаковый код, да ещё и переводить его на разные языки. С такой ситуацией я и столкнулся.

Постановка задачи

Нужно написать хранимую процедуру, которая на вход получает минимальную и максимальную длину генерируемого пароля, а на выходе выдаёт сам пароль, то есть строку, отвечающую следующим критериям:

Длина пароля случайна в заданных границах, при этом она не менее 6 символов (даже если пользователь запросил длину меньше).
Пароль состоит из случайно выбранных символов, включающих заглавные и строчные латинские буквы, цифры и специальные символы ($, # и другие).
Буквы, цифры и символы должны включаться в пароль более или менее равномерно, то есть мы не хотим, чтобы он состоял из одних только заглавных или строчных букв, или цифр, или символов.
Частота появления специальных символов меньше, чем у букв и цифр, потому что иначе такой пароль будет неудобно набирать, скажем, на клавиатуре смартфона, где специальные символы обычно набираются с переключением на отдельную экранную клавиатуру.
Пароль должен легко читаться, если он отображается на экране или напечатан (написан) на бумаге. То есть в нём не должны появляться символы, которые читаются неоднозначно.

Последний пункт основан на личном опыте. Когда сгенерированные пароли начали вводить люди, то очень скоро стало ясно, что некоторые символы во многих шрифтах слишком похожи и на экране, и на печати: почти во всех шрифтах похожи цифра 0 и заглавная буква O. В шрифтах без засечек (как Arial) не различаются строчная латинская l (L) и заглавная I (i) — обе выглядят как простая вертикальная палочка. Строчная l (L) в шрифтах с засечками (типа Times New Roman) неотличима от единицы. Люди путались, злились и начинали звонить в техподдержку. Из списка доступных символов пришлось выкинуть все спорные символы, и в нём оказалось 24 заглавные латинские буквы, 25 строчных букв (строчную «o» разрешаем: она в большинстве шрифтов не похожа на ноль) и 8 цифр.

Предупреждение (для начинающих)

Разумеется, следует понимать, что пароли в открытом виде в базе данных хранить не нужно. Для хранения давным-давно изобретены хэши (см., например, http://habrahabr.ru/post/210760/).

Решение

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

create or alter procedure GEN_PASSWORD (

MIN_LENGTH smallint = 8,



  MAX_LENGTH smallint = 10)

returns (

  PWD varchar(100))

as


/* Генератор паролей. Firebird 2.5


Автор:

  М.В.Демидов


Постановка задачи и прочая информация тут:

  http://mik-demidov.blogspot.com/2015/11/firebird-passgen.html


Входные параметры: 

  MIN_LENGTH - минимальная длина пароля,

  MAX_LENGTH - максимальная длина пароля


Выходные параметры:

  PWD - сгенерированный пароль


Примеры:

  GEN_PASSWORD(8, 10) = '94$wL!HC'

  GEN_PASSWORD(8, 10) = 'Wo86LN_uS'

  GEN_PASSWORD(8, 10) = 'g9r?LQcyGb'

*/




declare variable P_LENGTH smallint; -- длина пароля

declare variable DICE smallint; -- "игральная кость" — случайное число

declare variable LOWER_CHARS char(25) = 'abcdefghijkmnopqrstuvwxyz'; -- строчные буквы. Запрещённые символы: l (в большинстве шрифтов похож на 1 и заглавную I)

declare variable LOWER_CHARS_CHANCE smallint = 50; -- шанс на выпадение строчной буквы

declare variable UPPER_CHARS char(24) = 'ABCDEFGHJKLMNPQRSTUVWXYZ'; -- заглавные буквы. Запрещённые символы: I (в большинстве шрифтов похож на 1 и строчную l), O (похож на ноль)

declare variable UPPER_CHARS_CHANCE smallint = 35; -- шанс на выпадение заглавной буквы

declare variable DIGITS char(8) = '23456789'; -- цифры. Запрещённые символы: 0 (похож на букву O), 1 (похожа на заглавную I и строчную l)

declare variable DIGITS_CHANCE smallint = 20; -- шанс на выпадение цифры

declare variable ADD_CHARS char(11) = '$~+-_?!#@^&'; -- специальные символы

declare variable ADD_CHARS_CHANCE smallint = 15; -- шанс на выпадение специального символа

begin

  /* в пароле должно быть не менее 6 символов */

  if (MIN_LENGTH is null or MIN_LENGTH < 6) then

    MIN_LENGTH = 6;

  if (MIN_LENGTH > 100) then

    MIN_LENGTH = 100;

  if (MAX_LENGTH is null or MAX_LENGTH < MIN_LENGTH) then

    MAX_LENGTH = MIN_LENGTH;

  if (MAX_LENGTH > 100) then

    MAX_LENGTH = 100;

  if (MIN_LENGTH = MAX_LENGTH) then

    P_LENGTH = MAX_LENGTH;

  else 

    P_LENGTH = MAX_LENGTH - trunc(rand() * (MAX_LENGTH - MIN_LENGTH + 1));

  PWD = '';

  /* чтобы добиться некоторой равномерности распределения разных типов символов,

     мы после появления каждого следующего символа уменьшаем вероятность

     появления символа того же типа и увеличиваем вероятность появления всех

     остальных типов. При этом мы ещё не хотим, чтобы специальные символы

    (не буквы и не цифры) встречались слишком часто, поэтому с каждым появлением

     специального символа вероятность появления следующего специального символа

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

     относится к заглавным буквам */

  while (P_LENGTH > 0) do

  begin

    DICE = trunc(rand() * (LOWER_CHARS_CHANCE + UPPER_CHARS_CHANCE + DIGITS_CHANCE + ADD_CHARS_CHANCE));

    if (DICE < LOWER_CHARS_CHANCE) then

    begin

      PWD = PWD || substring(LOWER_CHARS from 1 + trunc(rand() * char_length(LOWER_CHARS)) for 1);

      LOWER_CHARS_CHANCE = LOWER_CHARS_CHANCE - 5;

      if (LOWER_CHARS_CHANCE <= 0) then

        LOWER_CHARS_CHANCE = 1;

      UPPER_CHARS_CHANCE = UPPER_CHARS_CHANCE + 4;

      DIGITS_CHANCE = DIGITS_CHANCE + 5;

      ADD_CHARS_CHANCE = ADD_CHARS_CHANCE + 2;

    end

    else

    if (DICE < LOWER_CHARS_CHANCE + UPPER_CHARS_CHANCE) then

    begin

      PWD = PWD || substring(UPPER_CHARS from 1 + trunc(rand() * char_length(UPPER_CHARS)) for 1);

      LOWER_CHARS_CHANCE = LOWER_CHARS_CHANCE + 5;

      UPPER_CHARS_CHANCE = UPPER_CHARS_CHANCE - 5;

      if (UPPER_CHARS_CHANCE <= 0) then

        UPPER_CHARS_CHANCE = 1;

      DIGITS_CHANCE = DIGITS_CHANCE + 5;

      ADD_CHARS_CHANCE = ADD_CHARS_CHANCE + 2;

    end

    else

    if (DICE < LOWER_CHARS_CHANCE + UPPER_CHARS_CHANCE + DIGITS_CHANCE) then

    begin

      PWD = PWD || substring(DIGITS from 1 + trunc(rand() * char_length(DIGITS)) for 1);

      LOWER_CHARS_CHANCE = LOWER_CHARS_CHANCE + 5;

      UPPER_CHARS_CHANCE = UPPER_CHARS_CHANCE + 4;

      DIGITS_CHANCE = DIGITS_CHANCE - 5;

      if (DIGITS_CHANCE <= 0) then

        DIGITS_CHANCE = 1;

      ADD_CHARS_CHANCE = ADD_CHARS_CHANCE + 2;

    end

    else

    begin

      PWD = PWD || substring(ADD_CHARS from 1 + trunc(rand() * char_length(ADD_CHARS)) for 1);

      LOWER_CHARS_CHANCE = LOWER_CHARS_CHANCE + 5;

      UPPER_CHARS_CHANCE = UPPER_CHARS_CHANCE + 4;

      DIGITS_CHANCE = DIGITS_CHANCE + 5;

      ADD_CHARS_CHANCE = ADD_CHARS_CHANCE - 5;

      if (ADD_CHARS_CHANCE <= 0) then

        ADD_CHARS_CHANCE = 1;

    end

    P_LENGTH = P_LENGTH - 1;

  end

  /* если пароль состоит только из символов одного типа (строчных или заглавных

     букв, цифр или специальных символов, то его нужно сформировать заново


     NB! Если минимальную длину пароля уменьшить до менее чем 3 символов, то

     тут гарантировано зависание процедуры. Поскольку у нас минимум 6 символов,

     то это не страшно */

  if (:PWD not similar to '%[[:UPPER:]+]%' or :PWD not similar to '%[[:LOWER:]+]%' or :PWD not similar to '%[[:DIGIT:]+]%' or :PWD not similar to '%[^[:ALNUM:]+]%') then

    PWD = (select PWD from GEN_PASSWORD(:MIN_LENGTH, :MAX_LENGTH));

  suspend;

end