Php mysql pdo query: fill up variable with query r

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.

回答1:

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

回答2:

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

回答3:

Try by change

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

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

access data as follows

foreach($query1 as $row)
{
 $result= $row['userid'];
}