PDO Подготовленные выражения и хранимые процедуры.

Многие из современных баз данных поддерживают подготовленные выражения. Что это, и с чем это едят? Представьте что это своего рода шаблон для SQL, который можно настроить и выполнить, используя различные параметры. Подготовленные выражения дают следующие преимущества:

• Запрос строится однажды, но может быть выполнен многократно с теми же самыми или другими параметрами. Когда запрос будет подготовлен, база данных проанализирует, соберет и выполнит его, это упрощённая схема выполнения вопроса. Для сложных запросов этот процесс может занять достаточно времени, это заметно замедлит получение результата. А если будет нужно повторить тот же самый запрос много раз, да ещё с различными параметрами, а если в цикле... шучу? При использовании подготовленного выражения запрос избегает повторной процедуры сборки, анализа и оптимизации. Это означает, что запросы, использующие подготовленные выражения расходуют меньше ресурсов и таким образом выполняются быстрее!

• Параметры для подготовленных выражений не нуждаются в явном экранировании, это берет на себя драйвер базы. Если запрос использует исключительно подготовленные выражения, никакая SQL - инъекция не пройдет (однако, это не означает что уязвимость не может появиться в другой части запроса, но это уже как говориться: кривые руки на всё от скуки...). Подготовленные выражения настолько мощны и полезны, что они - единственная функция, которую PDO будет эмулировать для драйверов, которые не поддерживают их! Это гарантирует, что запрос будет в состоянии использовать ту же самую парадигму доступа к данным, независимо от возможностей базы данных.

Многократная вставка данных, используя подготовленные выражения:

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

    $stmt = $dbh->prepare('INSERT INTO tbl_user (`id`,`username`,`password`,`email`) 
            VALUES (?,?,?,?),(?,?,?,?),(?,?,?,?) 
            ON DUPLICATE KEY UPDATE
            id=VALUES(id),
            username=VALUES(username),
            password=VALUES(password),
            email=VALUES(email)')
        ->execute(array(
            '1','foo-1','foo-pass-1','foo-email-1', // row 1
            '2','foo-2','foo-pass-2','foo-email-2', // row 2
            '3','foo-3','foo-pass-3','foo-email-3'  // row 3
        ));

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

  $stmt = $dbh->prepare('INSERT INTO REGISTRY (name, value) VALUES (:name, :value)');
  $stmt->bindParam(':name', $name);
  $stmt->bindParam(':value', $value);

  // Вставляем первую запись:
  $name = 'Вася';
  $value = 1;
  $stmt->execute();

  // Вставляем вторую запись с другим значением:
  $name = 'Петя';
  $value = 2;
  $stmt->execute();

Этот шаблон использует позиционные плейсхолдеры:

  $stmt = $dbh->prepare('INSERT INTO REGISTRY (name, value) VALUES (?, ?)');
  $stmt->bindParam(1, $name);
  $stmt->bindParam(2, $value);

  // Вставляем первую запись:
  $name = 'Вася';
  $value = 1;
  $stmt->execute();

  // Вставляем вторую запись с другим значением:
  $name = 'Петя';
  $value = 2;
  $stmt->execute();

Выборка данных, используя подготовленные выражения:

Этот пример получает данные, основанные на значении ключа, предоставляемого html - формой. Данные, полученные от пользователя, автоматически экранируются, таким образом, нет никакого риска SQL – инъекции:

  $stmt = $dbh->prepare('SELECT * FROM REGISTRY where name = ?');
  if ( $stmt->execute(array($_GET['name'])) )
  {
    while ($row = $stmt->fetch())
    {
       print_r($row);
    }
  }

Если драйвер базы данных поддерживает, то запрос может также связывать IN и OUT параметры. Параметры OUT обычно используются, чтобы получить данные из хранимых процедур. Выходные параметры OUT чуть более сложнее, в использовании чем входные параметры IN. Разработчик должен знать, насколько большое значение может принять данный параметр. Если значение, оказывается, больше чем указано, произойдёт ошибка.

Вызов хранимой процедуры с параметром OUT:

  $stmt = $dbh->prepare( 'CALL sp_returns_string(?)' );
  // Здесь четвёртым параметром указывается макс. величина, которую
  // может принять параметр:
  $stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000); 

  // Выполнение хранимой процедуры:
  $stmt->execute();
  echo "procedure returned $return_value\n";

Разработчики могут также определить параметры, которые являются INOUT. Синтаксис подобен, привязке IN и OUT параметров. В следующем примере строку «привет» мы передадим в хранимую процедуру параметром INOUT, и когда он же вернётся, значение «привет» заменится возвращаемым значением процедуры:

Вызов хранимой процедуры с параметром INOUT:

  $stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
  $value = "hello";
  $stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

  // Выполнение хранимой процедуры:
  $stmt->execute();
  print "procedure returned".$value;

Не правильное использование плейсхолдера:

  //Так нельзя:
  $stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%' ");
  $stmt->execute(array($_GET['name']));

  // Так правильно:
  $stmt = $dbh->prepare('SELECT * FROM REGISTRY where name LIKE ?');
  $stmt->execute(array('%$_GET[name]%'));