Hey guys, just wondering what is the best practice to do mysql queries in CI. Do I need to escape the query using db->query->escape?

Additionally, outputing stuff from the db so it doesn't break my html form what's the difference between using xss_escape and using php's htmlentities() functions.

Thanks in advance.

Member Avatar
diafol

Just wondering, has CI updated to using PDO or mysqli or is it still forcing users to go through mysql and escaping?

Your output depends on the input. Do you want html output or an "escaped/decoded tagless string". SOme people use a wysiwyg editor to produce (horrendous!) html content and they store that in the DB. So that may need to be output as html. User input generally shouldn't be stored as raw html. You can use striptags and other bits and bobs to keep 'clean / html-less' data in the db.

Sometimes, if using something like markup display with something like "syntaxhighlighter", you need to show html entities as in <p>Hello World</p>.

More info?

Just wondering, has CI updated to using PDO or mysqli or is it still forcing users to go through mysql and escaping?

No idea, I believe it uses mysql... I read somewhere that if I take user input using form_input() from the form helper library I should be OK?

User input generally shouldn't be stored as raw html. You can use striptags and other bits and bobs to keep 'clean / html-less' data in the db.

The likihood of the user needing to store html will be 1% so I guess I could store the data in the db as htmlentities. I thought this was bad practice. I thought you would store the values as is in the database then run it through htmlentites for output on my form?

I'm just confused on what xss_escape() does exactly?

cheers.

Member Avatar
diafol

The likihood of the user needing to store html will be 1% so I guess I could store the data in the db as htmlentities. I though this was bad practice. I thought you would store the values as is in the database then run it through htmlentites for output on my form?

Well, if you don't "allow" html, use strip_tags() before saving to DB - no entities to worry about :)

Perhaps I wasn't clear. I do not advise storing encoded html - that was just an example with regard to outputting 'code snippets' for example. You could store the 'raw code' as is if you really wanted and then encode on output. Do not store output-ready data in the DB as you may want to do a search on the data or even output in different formats (e.g. text, html, encoded, json...). If you store in one output format, then you may have inadvertently ...ed the pooch.

If you're using "basic mysql" (deprecated), then all input should be sanitized - that doesn't mean encoded.

Brains scrambled at the mo, hope it made some sense.

PS I no longer touch CI with a barge pole, so I can't comment further on its current functions.

There is one for PDO modification for CI here.

You can also take the user input by simply

$this->input->get('user_input', true);
$this->input->post('user_input',true);

the second parameter tells the input class that we want the first parameter to undergo XSS filter.

Without filtering at all, just put single parameter.

$this->input->get('user_input');

I am still hesitant on the binding method though, I don't know why? Maybe it is just me.

binding is like a walk on a park

$this_query = "SELECT * FROM your_table WHERE col1 = ? AND col2 = ? ";

$this->db->query($this_query, array('col 1 value ', 'col 2 value')); 

but I prefer something like this

$this_query = "SELECT * FROM your_table WHERE col1 =".$this->db->escape('from user')." AND col2 = ".$this->db->escape('from user ');