codeigniter mysql bitwise operator

Question

I have a value in my database (int) decimal. I have several constants in my codeigniter application linked to a binary 1 - 2 - 4 - 8 - ... .

I wanted to get all users with a specific binary acces level to be shown.

public function query($level = 0)
{
    $data = NULL;

    $this->db->select('user_id, user_email, user_name, user_firstname, user_imageammount, user_auth');
    $this->db->from('users');
    $this->db->where('user_auth' & $level);

    $query = $this->db->get();

    if ( $query->num_rows() > 0 )
    {
        $data = $query->result_array();
    }

    return $data;
}

When i run the query straight in my phpmyadmin i get the results that i need.

SELECT `user_id`, `user_email`, `user_name`, `user_firstname`, `user_imageammount`, `user_auth` FROM `users` WHERE `user_auth` & 1

When i run it in codeigniter i get a error.

Severity: Warning Message: A non-numeric value encountered

Any help or advice would be appriciated.

This is obviously wrong $this->db->where('user_auth' & $level); — ArtisticPhoenix
– ArtisticPhoenix, Commented Jan 25, 2019 at 23:33

ArtisticPhoenix · Accepted Answer · 2019-03-11 12:00:11Z

1

This is obviously wrong:

$this->db->where('user_auth' & $level);

For one thing you don't have the . to concatenate the string part to the non-string part. Even if you had 'user_auth' . & $level, it would still be wrong as the opperator & should be treated as any operater such as <,>,<> or != etc. In that it too should be a string. As it stands it's probably a syntax (or similar parse) error, a simple test tells me this:

Warning: A non-numeric value encountered in [...][...] on line 5

So try this instead:

 $this->db->where('user_auth & '.(int)$level);

A simple example makes this clear:

 #this is wrong
 $sql = "SELECT * FROM table WHERE user_auth" & 1;

 #this is correct
 $sql = "SELECT * FROM table WHERE user_auth & 1";

For this guy you can get away with just casting the level to an INT, this should sanitize it plenty fine as it's just an integer value. I have to make sure that it's understood that sanitizing is always important. Prepared queries are preferred but in cases where it's a simple INT (most likely drawn from class constants) we will be ok with just casting it as anything that is not an INT just becomes 0, which at worst won't return any results.

The point is, even when using class constants in a query it should be sanitized. Especially if that constant is passed in as a function argument. This is because the function could be used with 'unclean' data and you have no way to know if it's safe without looking though a lot of code. If you clean it right by the query you know it's always taken care of, etc.

Enjoy!

edited Mar 11, 2019 at 12:00

answered Jan 25, 2019 at 23:35

ArtisticPhoenix

21.7k2 gold badges27 silver badges38 bronze badges

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

4 Comments

ArtisticPhoenix Over a year ago

Sure it was a simple typo, you should turn on full error reporting when developing then you would see this as a warning. Let PHP tell you what is wrong.

PJC Over a year ago

Might be a good idea indeed. I am pretty new with development in php so all advice is welcome. Btw is the &~ (and not) operator possible in mysql? I seem to be just recieving the & operator.

Nick Over a year ago

@Pieter-JanCasteels &~ works fine in MySQL (e.g. SELECT 24&~8 returns 16) dbfiddle.uk/…

PJC Over a year ago

Very strange. In my mysql i don't recieve the correct information. With the & operator i recieve the once that countain a 1 and with the &~ i recieve them all ? i.imgur.com/7q6ckuC.jpg

Collectives™ on Stack Overflow

codeigniter mysql bitwise operator

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related