+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: DB queries NOT failing when they should. (/showthread.php?tid=56265)
DB queries NOT failing when they should. - El Forum - 12-06-2012
[eluser]boltsabre[/eluser]
I've just come across something a little strange.
When doing a DB SELECT query (either by using active records or a hand coded SQL statement executed with $this->db->query()) , and I pass it a bogus id like "1abc" it executes and returns the record 1. Likewise passing an id like "1_1" returns the record 1. "11abc" returns 11, and so on.
To my thinking this SHOULD not happen, I have no record 1abc, 1_1, 11abc, etc. The id column is an primary_key auto_increment int(11)
Is this a automatic "cleansing" done internally by CI, or my DB engine (MySQL)?
Is there anyway to disable it? I want id=1abc to fail.
Why? Because if it does fail then I can gracefully throw a 404 message/http header and be done with it. As it currently operates it's opening the door to duplicate content issues and now I have to incorporate the use of the rel=canonical tag.
Any thoughts?
DB queries NOT failing when they should. - El Forum - 12-06-2012
[eluser]Aken[/eluser]
This is normal behavior. CI interprets the bogus ID as a string and passes it accordingly (it has no idea what your DB structure is, it just does what it's told). MySQL doesn't do strict type comparisons when it comes to WHERE clauses, so the string is simply "not found" and no results are returned.
I'm unsure if there's an option you can enable in MySQL. A quick Google search didn't yield anything helpful.
You should sanitize/check the ID prior to sending it to your query.
DB queries NOT failing when they should. - El Forum - 12-06-2012
[eluser]boltsabre[/eluser]
Thanks for the quick reply champ!
Quote: MySQL doesn’t do strict type comparisons when it comes to WHERE clauses, so the string is simply “not found” and no results are returned.
That's what I'm getting at, it's NOT doing that, it's returning results for bogus string ids such as "1abc". I certainly don't have "1abc" in my table as an ID. Am I still missing something? Is MySQL stripping the non-int characters out because it knows my column in int(11)?