Welcome Guest, Not a member yet? Register   Sign In
How to output SQL statement with Active Record
#1

[eluser]GeoffL[/eluser]
Sorry for the newbie question, but I only started playing with CI a week or so ago:

I have the following code that's not giving me the expected result:
Code:
function authenticate(){        
    $this->db->from('csw_user');
    $this->db->where('username', $this->db->escape($this->input->post('username')));
    $this->db->where('password', sha1($this->input->post('password')));
    $table = $this->db->get();
    if ($table->num_rows() > 0){
        $success = TRUE;
    } else {
        $success = FALSE;
    }
    return $success;
}

If I wasn't using CI, one of the first things I'd do would be to echo the SQL statement and then copy and paste it into the MySQL CLI (assuming there wasn't an obvious error). However, with Active Record, I can't find anything that gives me access to the SQL that would be passed to MySQL. Is this possible, or do I need to forget about Active Record?

TIA,

Geoff
#2

[eluser]jmadsen[/eluser]
Code:
$this->db->last_query();
#3

[eluser]GeoffL[/eluser]
Thanks. As it only lets me see the SQL after it's been sent to the DBMS and most of the time I need this debugging trick when the SQL causes a run-time error (e.g. SQL syntax errors), I at first thought it would be of limited use. However, I deliberately introduced an SQL error and note that CI helpfully included the SQL in the error message it generated.

So while it's not quite what I was looking for, it has let me identify the cause of the problem for this particular case. Running the code in my previous post with username="username" and password="password" produces:
Code:
SELECT *
FROM (`csw_user`)
WHERE `username` = '\'username\''
AND `password` = '5baa61e4c9b93f3f0682250b6cf8331b7ee68fd8'
IOW, CI seems to implement its own version of the infamous 'magic quotes'. I did check using phpinfo() and all variants of magic quotes are 'off' on my system (PHP 5.3.5-1ubuntu7.2 under Ubuntu 11.04).

I experimented and now know that I can get rid of the issue for now by changing:
Code:
$this->db->where('username', $this->db->escape($this->input->post('username')));
to
Code:
$this->db->where('username', mysql_real_escape_string($this->input->post('username')));
However, that reduces portability and I may switch to Postgres at some time in the future.

I know that the docs say that db->escape() automatically adds single quotes around the data so you don't have to, but surely it's a common requirement to escape an input value when passed as a parameter of db->where() - and that's somewhere this 'feature' makes no sense. Is this a bug, or am I missing something?

TIA,

Geoff
#4

[eluser]Aken[/eluser]
All values passed to the where() method are escaped automatically. You're effectively double escaping, which is why you're getting the quotes and slashes in your query. You should only need to pass the post value.
#5

[eluser]GeoffL[/eluser]
[quote author="Aken" date="1312027608"]All values passed to the where() method are escaped automatically. You're effectively double escaping, which is why you're getting the quotes and slashes in your query. You should only need to pass the post value.[/quote]
Thanks - I'd missed that and only spotted the bit that says that it's a good idea to escape everything before submitting it into your database. That said, I wouldn't have expected any number of escapes to introduce quotes that weren't present in the input value!
#6

[eluser]Aken[/eluser]
You're confusing quotes with back ticks, which are what surrond the table and column names. You can turn that off if you want.

See "Protecting Identifiers"




Theme © iAndrew 2016 - Forum software by © MyBB