PHP PDO Error in Select Statement

Question

I am having some issues with PDO. First issue is that in my connection string, I cannot use variables defined in another file (tried include & require). It gives me an error and shows the connectionstring having the variable name instead of the value (yes, single and double quotes were tried).

Second issue and more pressing is that I am getting an error when trying to validate a login. I had it working with the old mysqli, so I know that the html is valid, just not the new logic. Attached is the function I am using:

function validateuser($un, $em)
{
    try
    {
        $db = new PDO("mysql:host=localhost;dbname=XX", 'aa', 'bb', array(
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        ));
        $stmt = $db->query("SELECT AcctType FROM usr WHERE LOGIN_ID=? or CONTACT_EMAIL=?;");
        $stmt->execute(array(
            $un,
            $em
        ));
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    catch(exception $e)
    {
        echo $e;
    }
}

the exact error i get is:

Exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2031 ' in db_backend.php:110 Stack trace: #0 db_backend.php(110): PDO->query('SELECT AcctType...') #1 newuser.php(42): validateuser('Nefer', 'neferitous@blah...') #2 {main}

Thanks in advance!!

Answer 1

In order to bind parameters, you need to use a prepared statement and not a query.

Try the following:

$stmt = $db->prepare("SELECT AcctType FROM usr WHERE LOGIN_ID=? or CONTACT_EMAIL=?;");
$stmt->execute(array(
    $un,
    $em
));

Answer 2

For your first problem, I'm pretty sure you could break the string and concatenate the variables in:

PDO("mysql:host=".$host.";dbname=".$dbname, $user, $pass,

Answer 3

Here is my final code to make this part of the PDO statement work:

function validateuser($un, $em) {
    try {
        $db = new PDO(
            "mysql:host=localhost;dbname=db",
            'id',
            'pass',
            array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
        );
        $stmt = $db->prepare("SELECT ACCTTYPE FROM users WHERE LOGIN_ID=? or CONTACT_EMAIL=?;");
        $stmt->bindParam(1, $un, PDO::PARAM_STR, 15);
        $stmt->bindParam(2, $em, PDO::PARAM_STR, 150);
        $stmt->execute();
        $result = $stmt->fetch();
        return $result["ACCTTYPE"];
    } catch (exception $e) {
        echo $e;
    }
}