Multi-parameterized SQL syntax invalid using WHERE AND [duplicate]

Question

Below is the code in my model, I'm using Codeigniter, I'm sure there's a simple problem with it but I've been trying for a long time, any ideas?

<?php
    class Users_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    public function checkLogin($username, $pass) {
        $sql = "SELECT COUNT(*) FROM Users WHERE username=? AND password=?;";
        $query = $this->db->query($sql, $username, sha1($pass));

        if ($query -> num_rows() == 1) {
            return True;
        } else {
            return False;
        }
    }
    }
?>

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AND password=?' at line 1

Can you be more precise about the problem you have ? What PHP error do you see ? — lovasoa
– lovasoa, Commented Nov 11, 2016 at 21:28
Although it won't solve the problem, why not do SELECT COUNT(*) FROM Users WHERE username=? AND password=?;? — Andrew Morton
– Andrew Morton, Commented Nov 11, 2016 at 21:31

lovasoa · Accepted Answer · 2016-11-11 21:49:18Z

2

If $this->db is a PDO object, than its query method doesn't allow you to use argument binding.

You will have to use a prepared statement. Your code would then look like:

$sql = "SELECT COUNT(*) as count FROM Users WHERE username=:user AND password=:pass";
$sth = $this->db->prepare($sql);
$sth->execute(array(':user' => $username, ':pass' => sha1($pass)));
$count = $sth->fetch(PDO::FETCH_COLUMN, 'count');

edited Nov 11, 2016 at 21:49

answered Nov 11, 2016 at 21:34

lovasoa

6,9261 gold badge45 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bill Karwin Over a year ago

For what it's worth, using named parameter placeholders is not mandatory in PDO. You can use positional parameters (? placeholders). But you cannot use both styles in the same SQL query.

mickmackusa Nov 6 at 5:37

This is not appropriate in a CodeIgniter application. CodeIgniter has its own database interacting methods.

js864 · Accepted Answer · 2016-11-12 17:29:51Z

1

Today I found that the below worked, the parameters for the SQL statement just needed to be in a array.

$query = $this->db->query($sql, array($username, sha1($pass)));

answered Nov 12, 2016 at 17:29

js864

615 bronze badges

Collectives™ on Stack Overflow

Multi-parameterized SQL syntax invalid using WHERE AND [duplicate]

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related