Prepared Statements (PDO) copy|paste Beispiele.

cottton · 24. April 2020

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.
// ...
}

Alles anzeigen