dongzhiqi0332
2017-07-19 07:30
浏览 105
已采纳

php mysql pdo查询:用查询结果填充变量

I have a website to easily generate ProFTPD users. And now, I am securing my website against sql injection attacks, to do that I am changing all mysqli queries to pdo queries with prepared statements.

But I still couldn't find out, how to save sql query results in variable.

.
.
.

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";
}

When I run this code, the variable $result is emtpy.

I hope somebody could help me.

Thanks in advance.

图片转代码服务由CSDN问答提供 功能建议

我有一个网站可以轻松生成ProFTPD用户。 现在,我正在保护我的网站免受SQL注入攻击,为此我正在使用预处理语句将所有mysqli查询更改为pdo查询。

但我仍然无法找到,如何 将sql查询结果保存在变量中。

 <代码> 
 
 
 
用户名= $ _ POST [ '用户名'];。。。。
 
 
 
 
 
 $的 pdo = new PDO('mysql:host = localhost; dbname ='db','root','PW'); 
 $ query1 = $ pdo-&gt; prepare('select * from user where userid =:username'  ); 
 $ query1-&gt; execute(array('username'=&gt; $ username)); 
 
foreach($ query1 as $ row)
 {
 $ result = $ row-&gt; userid;  
 
 
 
 
if($ result == $ username)
 {
 echo“用户名已被占用”; 
} 
   
 
 

当我运行此代码时,变量$ result是emtpy。

我希望有人可以帮助我。

提前致谢。

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dozabt4329 2017-07-19 07:34
    已采纳

    You should use PDOStatement::fetch http://php.net/manual/en/pdostatement.fetch.php

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • douyong5476 2017-07-19 07:34

    Try by change

    $query1= $pdo->prepare('select * from users where userid=:username');
    

    to

    $query1= $pdo->query('select * from users where userid=:username');
    

    access data as follows

    foreach($query1 as $row)
    {
     $result= $row['userid'];
    }
    
    评论
    解决 无用
    打赏 举报
  • douluanji8752 2017-07-19 08:11

    Even if you already have a solution, you might find helpful a complete example of using PDO prepared statements together with exception handling:

    Notes:

    • Don't fetch all fields, but just userid. Limit fetching to only one record.
    • Always use exception handling to catch and immediately handle exceptions.
    • Always use prepared statements to avoid MySQL injection.
    • The items in the fetched data array are addressed as normal array items.
    • "functions.php" contains all needed functions and is included in "index.php". I didn't give you an OOP code, but you should implement one instead of using plain functions. My code serves as starting point for you.

    PDO prepared statements and exception handling in PHP:

    index.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';
    }
    

    functions.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));
    }
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题