CodeIgniter Where value IN (Field1,Field2)

Question

I need to set query in my auth script, where in WHERE clause I want to use '_MY_POST_VALUE' IN (Field1, Field2)

Final query will be something like this:

SELECT * FROM 'myprefix_users' WHERE 'myemail@email.com' IN ('EMAIL','LOGIN') AND PASSWORD=SHA1('mypassword')

I've tried to do this:

$this->db->where("'" . mysql_escape_string($_POST['login']) . "' IN (EMAIL,LOGIN)", NULL, FALSE);
$this->db->where("PASSWORD=SHA1('" . mysql_real_escape_string($_POST['password']) . "')");
$userdb = $this->db->get('users');

..but CodeIgniter set prefix to my login/email value and send 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 ''test@test.com' IN (EMAIL,LOGIN) AND PASSWORD=SHA1('123')' at line 3

SELECT * FROM (`myprefix_users`) WHERE myprefix_'test@test.com' IN (EMAIL,LOGIN) AND PASSWORD=SHA1('123')

I need to use db_prefix in table name, but I don't need it in my WHERE clause, even third param (FALSE) in ->where() don't work for me :(

How can I solve my problem? Any ideas?

you can do with your custum query as well :) – Suleman Ahmad Jul 05 '12 at 15:45 — Suleman Ahmad, Jul 05 '12 at 15:45

score 2 · Accepted Answer · answered Jul 05 '12 at 15:52

Try using Codeigniter's the Input class. It will simplify your code:

$login = $this->input->post('login', TRUE);
$password = $this->input->post('password', TRUE);
$this->db->where("'$login' IN ", "('EMAIL','LOGIN')", FALSE);
$this->db->where("PASSWORD", "SHA1('$password')", FALSE);
$userdb = $this->db->get('users');

Steven Lu · Answer 2 · 2012-07-05T15:48:53.563

1

First off, you don't need all the unnecessary functions in your database statements.

Why not do:

$user = $_POST['user'];
$pass = sha1($pass);

$this->db->where("(email = '$user' OR username = '$user') AND password = '$pass'");
$query = $this->db->get('users');

You don't need to worry about mysql escape since the CodeIgniter class will automatically escape them for you.

edited Jul 05 '12 at 15:48

answered Jul 05 '12 at 15:43

Steven Lu

2,150
1
24
33

Actually very similar to this question: http://stackoverflow.com/questions/9870583/codeigniter-active-record-where-or-where – Steven Lu Jul 05 '12 at 15:47
`OR` is bad solution, because it will make request slow. Read about how `OR` works in low level of MySQL engine. And why I should allocate memory for variables, that will duplicate my `$_POST` vars? – Egor Sazanovich Jul 05 '12 at 17:04
Sorry, it made the syntax highlighter a little better. You don't need to duplicate the variables. As from the answer above, using the input class will help you significantly. – Steven Lu Jul 05 '12 at 17:44

CodeIgniter Where value IN (Field1,Field2)

2 Answers2