MYSQL with PDO - Column not found

Question

I'm using plain PHP with PDO for dealing with MySQL database, I'm developing a login system and when I try to verify matching database record this is my code:

    $query = "SELECT id FROM users WHERE username=`:u` AND password=`:p`;";
    try{
        $q = $db->prepare($query);
        $q->bindParam(':u', $this->_username, PDO::PARAM_INT);
        $q->bindParam(':p', $this->_passmd5, PDO::PARAM_INT);
        if($q->execute()&& ($q->rowCount() == 1)){
            return true;
        }else{
            return false;
        }
    }catch(PDOException $e){
        $this->_errors[] = $e->getMessage();
    }

when I submit the user name and password i.e. (admin/password). it gives me that error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column ''admin'' in 'where clause'

Possible duplicate of [When to use single quotes, double quotes, and backticks?](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) — Qirel, Aug 12 '16 at 11:02
Backticks `\`` are for column-names and database-names. Single-quotes `'` are for wrapping around strings. When dealing with prepared statements, you should use nothing around the placeholder. Also, I'm guessing your username and passwords aren't integers, but strings, so `PDO::PARAM_INT` is kind of out of place here. Should be `PDO::PARAM_STR` — Qirel, Aug 12 '16 at 11:09
I don't see how that error could come from the query you show. There's no `admin` in the query. The error should say `Unknown column ":u"`. — Barmar, Aug 12 '16 at 12:18

score 1 · Accepted Answer · answered Aug 12 '16 at 11:03

1

Try to remove the backticks around :u and :p from your select query. This type of backticks are for defining columns in mysql.

answered Aug 12 '16 at 11:03

Whiteulver

828
7
12

score 1 · Answer 2 · answered Aug 12 '16 at 11:04

1

I assume you try to bind strings and not integers.

The quotes are add when binded :

$query = "SELECT id FROM users WHERE username=:u AND password=:p;";

$q->bindParam(':p', $this->_passmd5, PDO::PARAM_STR, length);

answered Aug 12 '16 at 11:04

iguypouf

770
4
15

score 1 · Answer 3 · answered Aug 12 '16 at 11:08

1

First, your logic quite good for that scenario. Is your username is integer. if yes, then ok not PDO::PARAM_STR

answered Aug 12 '16 at 11:08

vinay kumar reddy

127
7

MYSQL with PDO - Column not found

3 Answers3