Prepared Statements (PDO) copy|paste Beispiele.

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