我有一个网站可以轻松生成ProFTPD用户。现在，我正在保护我的网站免受 sql 注入攻击，为此，我正在将所有 mysqli 查询更改为带有预准备语句的 pdo 查询。
但是我仍然找不到，如何将sql查询结果保存在变量中。
.
.
.
username=$_POST['username'];
.
.
.
$pdo = new PDO('mysql:host=localhost;dbname='db', 'root', 'PW');
$query1= $pdo->prepare('select * from users where userid=:username');
$query1->execute(array('username' => $username));
foreach($query1 as $row)
{
$result= $row->userid;
}
if($result == $username)
{
echo "Username is already taken";
}
当我运行此代码时，变量$result是emtpy。
我希望有人能帮助我。
提前谢谢。
即使您已经有了解决方案，您也可能发现将 PDO 准备语句与异常处理一起使用的完整示例很有用：
笔记：
- 不要获取所有字段，而只是
userid。将提取限制为仅包含一条记录。 - 始终使用异常处理来捕获并立即处理异常。
- 始终使用预准备语句以避免 MySQL 注入。
- 提取的数据数组中的项作为普通数组项进行寻址。
- "functions.php"包含所有需要的函数，并包含在"index.php"中。我没有给你一个OOP代码，但你应该实现一个而不是使用普通函数。我的代码可以作为您的起点。
PHP 中的 PDO 准备语句和异常处理：
索引.php：
<?php
require_once 'functions.php';
/*
* ----------------
* Database configs
* ----------------
*/
define('MYSQL_HOST', '...');
define('MYSQL_PORT', '3306');
define('MYSQL_DATABASE', '...');
define('MYSQL_CHARSET', 'utf8');
define('MYSQL_USERNAME', '...');
define('MYSQL_PASSWORD', '...');
/*
* -------------------------
* Start program
* -------------------------
*/
// Activate error reporting (only on development).
activateErrorReporting();
try {
// Validate user name.
if (!isset($_POST['username'])) {
throw new Exception('No user name provided!');
}
// Get user name.
$username = $_POST['username'];
// Create db connection.
$connection = createConnection(
MYSQL_HOST
, MYSQL_DATABASE
, MYSQL_USERNAME
, MYSQL_PASSWORD
, MYSQL_PORT
, MYSQL_CHARSET
);
// Define sql statement.
$sql = 'SELECT userid FROM users WHERE userid = :username LIMIT 1';
// Prepare and check sql statement (returns PDO statement).
$statement = $connection->prepare($sql);
if (!$statement) {
throw new Exception('The SQL statement can not be prepared!');
}
// Bind values to sql statement parameters.
$statement->bindValue(':username', $username, getInputParameterDataType($username));
// Execute and check PDO statement.
if (!$statement->execute()) {
throw new Exception('The PDO statement can not be executed!');
}
// Fetch person details.
$fetchedData = $statement->fetchAll(PDO::FETCH_ASSOC);
if (!$fetchedData) {
throw new Exception('Fetching data failed!');
}
closeConnection($connection);
} catch (PDOException $pdoException) {
// On development.
printData($pdoException, TRUE);
// On production.
// echo $pdoException->getMessage();
exit();
} catch (Exception $exception) {
// On development.
printData($exception, TRUE);
// On production.
// echo $exception->getMessage();
exit();
}
// For testing purposes.
printData($fetchedData, TRUE);
if (count($fetchedData) > 0) {
echo 'Username is already taken';
}
功能.php：
<?php
/*
* --------------------------------------------------
* Data access functions
* --------------------------------------------------
*/
/**
* Create a new db connection.
*
* @param string $host Host.
* @param string $dbname Database name.
* @param string $username Username.
* @param string $password Password.
* @param string $port [optional] Port.
* @param array $charset [optional] Character set.
* @param array $options [optional] Driver options.
* @return PDO Db connection.
*/
function createConnection($host, $dbname, $username, $password, $port = '3306', $charset = 'utf8', $options = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_PERSISTENT => true,
)) {
$dsn = getDsn($host, $dbname, $port, $charset);
$connection = new PDO($dsn, $username, $password);
foreach ($options as $key => $value) {
$connection->setAttribute($key, $value);
}
return $connection;
}
/**
* Create a mysql DSN string.
*
* @param string $host Host.
* @param string $dbname Database name.
* @param string $port [optional] Port.
* @param array $charset [optional] Character set.
* @return string DSN string.
*/
function getDsn($host, $dbname, $port = '3306', $charset = 'utf8') {
$dsn = sprintf('mysql:host=%s;port=%s;dbname=%s;charset=%s'
, $host
, $port
, $dbname
, $charset
);
return $dsn;
}
/**
* Close a db connection.
*
* @param PDO $connection Db connection.
* @return void
*/
function closeConnection($connection) {
$connection = NULL;
}
/**
* Get the data type of a binding value.
*
* @param mixed $value Binding value.
* @return mixed Data type of the binding value.
*/
function getInputParameterDataType($value) {
$dataType = PDO::PARAM_STR;
if (is_int($value)) {
$dataType = PDO::PARAM_INT;
} elseif (is_bool($value)) {
$dataType = PDO::PARAM_BOOL;
}
return $dataType;
}
/*
* --------------------------------------------------
* Print functions
* --------------------------------------------------
*/
/**
* Print data on screen.
*
* @param mixed $data Data to print.
* @param bool $preformatted Print preformatted if TRUE, print normal otherwise.
* @return void
*/
function printData($data, $preformatted = FALSE) {
if ($preformatted) {
echo '<pre>' . print_r($data, true) . '</pre>';
} else {
echo $data;
}
}
/*
* --------------------------------------------------
* Error reporting functions
* --------------------------------------------------
*/
/**
* Toggle error reporting.
*
* @param integer $level Error level.
* @param bool $display_errors Display errors if TRUE, hide them otherwise.
* @return void
*/
function activateErrorReporting($level = E_ALL, $display_errors = TRUE) {
error_reporting($level);
ini_set('display_errors', ($display_errors ? 1 : 0));
}
你应该使用 PDOStatement：：fetch http://php.net/manual/en/pdostatement.fetch.php
通过更改尝试
$query1= $pdo->prepare('select * from users where userid=:username');
自
$query1= $pdo->query('select * from users where userid=:username');
按如下方式访问数据
foreach($query1 as $row)
{
$result= $row['userid'];
}