Query bindings keep unwanted quotes around IS NULL

Query bindings keep unwanted quotes around IS NULL - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Query bindings keep unwanted quotes around IS NULL (/showthread.php?tid=65767)

Query bindings keep unwanted quotes around IS NULL - meow - 07-21-2016

Hmm, CI throws this error:

Code:
A Database Error Occurred

Error Number: 1064

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 ''IS NULL' AND plantauth.contributor_id IS NULL ORDER BY 'plantitems.id ASC' at line 4

SELECT * FROM plantitems LEFT JOIN plantauth ON plantitems.id = plantauth.plantid WHERE plantauth.owner_id = '51' AND plantitems.death 'IS NULL' AND plantauth.contributor_id IS NULL ORDER BY 'plantitems.id ASC'

Filename: models/Logbook_model.php

Line Number: 29

Here's a screenshot of the model.

[Image: JukAGq.png]

WANT to pass a variable's text into the query binding but CI is passing the quotes around IS NULL and the SQL query doesnt like that. (Shown in the fourth paragraph of the error).

Am I missing a parameter like 'escape' (if one exists)?

RE: Query bindings keep unwanted quotes around IS NULL - meow - 07-21-2016

I was trying to avoid using the Query Builder Class, but had to.

Final code for now:

[Image: Zaau2K.png]

RE: Query bindings keep unwanted quotes around IS NULL - InsiteFX - 07-21-2016

Did you try using double quotes for the query body?

RE: Query bindings keep unwanted quotes around IS NULL - Avenirer - 07-22-2016

A stupid question: If it's you who defines the value of $archived (in the code...), why put it as parameter and not simply concatenate it inside the string of the query?

RE: Query bindings keep unwanted quotes around IS NULL - Narf - 07-22-2016

(07-22-2016, 06:33 AM)Avenirer Wrote: A stupid question: If it's you who defines the value of $archived (in the code...), why put it as parameter and not simply concatenate it inside the string of the query?

Not a stupid question.

RE: Query bindings keep unwanted quotes around IS NULL - meow - 07-22-2016

(07-21-2016, 03:22 PM)InsiteFX Wrote: Did you try using double quotes for the query body?

Man, I tried everything lol. Tried concatenating and changing up the quote types all around. You think its worth looking into? I know ci is doing it to produce safer queries.

(07-22-2016, 06:33 AM)Avenirer Wrote: A stupid question: If it's you who defines the value of $archived (in the code...), why put it as parameter and not simply concatenate it inside the string of the query?

That's a good question and its fair to ask a stupid question if you are wanting to learn something (or you asking in order to hack my website? kidding..) That parameter is handy when using it from the controller:

Setting it to TRUE in the controller calls the model method and
setting it to FALSE can change things up in a quickie (not a seasoned php writer/veteran here).

It should probably help you to see the SQL design to understand this further, let me know.

Controller preview

Code:
$data['my_active_logbooks'] = $this->logbook_model->get_my_logs($this->session->userdata('person_id'),FALSE,$this->session->userdata('sorting'));

$data['my_archived_logbooks'] = $this->logbook_model->get_my_logs($this->session->userdata('person_id'),TRUE,$this->session->userdata('sorting'));

I probably misunderstood your question though.

RE: Query bindings keep unwanted quotes around IS NULL - InsiteFX - 07-22-2016

Did you try doing a echo var_dump($data); to see what you are getting back?

RE: Query bindings keep unwanted quotes around IS NULL - mwhitney - 07-22-2016

The issue here is that you're trying to bind both the value (parameter) and the operator to your prepared statement. Further, you're trying to use a string to represent null, which usually doesn't work even if you do everything else properly. You're only allowed to bind the value to the '?' token in your statement, so you are going to have to change your statement based on whether you want to check for 'is null' or 'is not null'. In many cases, this would mean you need two separate prepared statements, or a stored procedure which changes the query based on the input (but you probably still wouldn't pass 'is null' or 'is not null' as your values).