Warum Prepared Statements und was ist das: Sichere Programmierung mit PHP
Beispiele, die (hoffentlich) für sich selbst sprechen,
und eigtl. auf jedem System laufen sollten:
Kommentare nur in englisch.
PHP
- /**
- * We are using a simple user table.
- * (This table is not mean to make sense.)
- *
- * CREATE TABLE `user` (
- * `id` int(6) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
- * `firstname` varchar(30) NOT NULL,
- * `lastname` varchar(30) NOT NULL,
- * `email` varchar(50) NOT NULL
- * );
- * INSERT INTO `user` (`firstname`, `lastname`, `email`)
- * VALUES ('hans', 'foo', 'hans@host.de'),
- * ('joe', 'bar', 'joe@host.de'),
- * ('peter', 'baz', 'peter@host.de');
- *
- * SELECT * FROM `user`;
- * +----+-----------+----------+---------------+
- * | id | firstname | lastname | email |
- * +----+-----------+----------+---------------+
- * | 1 | hans | foo | hans@host.de |
- * | 2 | joe | bar | joe@host.de |
- * | 3 | peter | baz | peter@host.de |
- * +----+-----------+----------+---------------+
- */
- /**
- * Simulate some user input via POST.
- */
- $_POST['offset'] = 1;
- $_POST['limit'] = 100;
- $_POST['email'] = 'somebody@host.de';
- $_POST['table'] = 'user';
- $_POST['columns_to_select'] = ['firstname', 'lastname', 'email'];
- /**
- * Info: "what means $dbh, $sth or $stmt?"
- *
- * $dbh = "Database Handle"
- * $stmt = "Statement"
- *
- *
- * Note:
- * Using exception error mode.
- *
- * @link See https://www.php.net/manual/de/pdo.error-handling.php.
- */
- /**
- * Connect.
- *
- * @link https://www.php.net/manual/de/pdo.connections.php
- */
- $host = '';
- $port = 3306;
- $database = '';
- $user = '';
- $password = '';
- $options = [
- /**
- * Request a persistent connection, rather than creating a new connection.
- *
- * @link http://php.net/manual/en/pdo.constants.php
- */
- \PDO::ATTR_PERSISTENT => true,
- /**
- * Command to execute when connecting to the MySQL server.
- *
- * @link http://php.net/manual/en/ref.pdo-mysql.php
- *
- * Info: with sql_mode STRICT_ALL_TABLES you f.e. cannot accidentally insert 'abcd' into VARCHAR(3) column.
- * MySQL would return an error like "Data too long ...".
- * Without this mode you would inset i.e. 'abc', which is imo wrong.
- */
- \PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARSET 'utf8', NAMES 'utf8', sql_mode = 'STRICT_ALL_TABLES';",
- ];
- try {
- $dbh = new \PDO(
- "mysql:host={$host}:{$port};dbname={$database}",
- $user,
- $password,
- $options
- );
- /**
- * Set exception error mode. @link https://www.php.net/manual/de/pdo.error-handling.php.
- *
- * @link http://php.net/manual/en/pdo.constants.php
- */
- $dbh->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
- } catch (\PDOException $e) {
- // Error handling. In example echo and exit.
- echo "Cannot connect to database. Error: {$e->getMessage()}\r\n";
- exit(1);
- }
- /**
- * Query with placeholder.
- *
- * @link https://www.php.net/manual/de/pdo.prepared-statements.php
- */
- $stmt = $dbh->prepare("SELECT * FROM `user` WHERE `email` = :email;");
- $params = [
- // Note: you can use ':email' or 'email' as key. This does not matter.
- 'email' => $_POST['email'],
- ];
- $stmt->execute($params);
- $stmt->setFetchMode(\PDO::FETCH_ASSOC);
- if ($stmt->rowCount()) {
- // Found one or more rows.
- while ($row = $stmt->fetch()) {
- // Do something with the row. In example echo.
- echo var_export($row, true) . PHP_EOL;
- }
- } else {
- // No row found.
- // ...
- }
- /**
- * Example: offset, limit
- *
- * LIMIT and OFFSET values MUST be placed as integer into the query (-template).
- * Therefor we need to use bindValue() to "tell" the MySQL server to use those values as int.
- */
- $stmt = $dbh->prepare("SELECT * FROM `user` LIMIT :offset, :limit;");
- $stmt->bindValue('limit', $_POST['limit'], PDO::PARAM_INT);
- $stmt->bindValue('offset', $_POST['offset'], PDO::PARAM_INT);
- $stmt->execute();
- $stmt->setFetchMode(\PDO::FETCH_ASSOC);
- if ($stmt->rowCount()) {
- // Found one or more rows.
- while ($row = $stmt->fetch()) {
- // Do something with the row. In example echo.
- echo var_export($row, true) . PHP_EOL;
- }
- } else {
- // No row found.
- // ...
- }
- /**
- * Example: table and column names
- *
- * Table and column names CAN NOT be placed via placeholders.
- * If you MUST create a query dynamically then its the most secure way is to use
- * a whitelist.
- * Do NOT use a blacklist or any "filter bogus".
- * Just accept what you know, and deny anything else.
- *
- * Note: key is table|column name, values indicates if this active.
- * From my tests isset() is faster than in_array(),
- * you cannot add the same key more than once,
- * and you can disable offsets (f.e. by other conditions).
- *
- * IMPORTANT: those whitelists MUST NOT be created dynamically.
- * In best case they should be defined as constant in your config.
- */
- const TABLE_WHITELIST
- = [
- 'user' => true,
- ];
- const COLUMN_TO_SELECT_WHITELIST
- = [
- 'id' => false,
- 'firstname' => true,
- 'lastname' => true,
- 'email' => true,
- ];
- // Validate table name.
- if (!isset(TABLE_WHITELIST[$_POST['table']])) {
- // Error handling. In example echo and exit.
- echo "Invalid table '" . htmlspecialchars($_POST['table']) . "' given.\r\n";
- exit(1);
- }
- $tableName = $_POST['table'];
- // Validate column names.
- foreach ($_POST['columns_to_select'] as $columnName) {
- if (!isset(COLUMN_TO_SELECT_WHITELIST[$columnName])) {
- // Error handling. In example echo and exit.
- echo "Invalid column to select '" . htmlspecialchars($_POST['column'])
- . "' given.\r\n";
- exit(1);
- }
- }
- $columnsString = '`' . implode('`, `', $_POST['columns_to_select']) . '`';
- // Using pre-validated vars for table name and column names in query.
- $stmt
- = $dbh->prepare("SELECT {$columnsString} FROM `{$tableName}` WHERE `email` = :email;");
- $params = [
- 'email' => $_POST['email'],
- ];
- $stmt->execute($params);
- $stmt->setFetchMode(\PDO::FETCH_ASSOC);
- if ($stmt->rowCount()) {
- // Found one or more rows.
- while ($row = $stmt->fetch()) {
- // Do something with the row. In example echo.
- echo var_export($row, true) . PHP_EOL;
- }
- } else {
- // No row found.
- // ...
- }