[El Forum]

[eluser]CodeIgniter Fan[/eluser]
I am developing matrimonial site on which I am query the database on multiple talbes which contains both AND & OR operator Now this is my query


SELECT DISTINCT pinfo.id, pinfo.name FROM partner_primary_info as pinfo INNER JOIN partner_religious_background relbackg ON pinfo.id=relbackg.candidate_id INNER JOIN partner_astro_data astro ON relbackg.candidate_id=astro.candidate_id INNER JOIN partner_family_details familyd ON astro.candidate_id=familyd.candidate_id INNER JOIN partner_life_style lifestyle ON familyd.candidate_id=lifestyle.candidate_id INNER JOIN partner_education_occupation eduocc ON lifestyle.candidate_id=eduocc.candidate_id INNER JOIN partner_residence resi ON eduocc.candidate_id=resi.candidate_id WHERE pinfo.gender = 'Male' AND pinfo.age >= '25' AND pinfo.age <= '27' AND pinfo.marital_status = 'Single' AND pinfo.no_of_children = 'None' OR pinfo.no_of_children = '' AND pinfo.height >= '4.05' AND pinfo.height <= '7.00' AND pinfo.physical_status = '2'


I am generating the query string using active record class now in the above query if I want to add brackets on both side of no_of_children field that I had shown bold above in query.

Is it possible to add brackets using active record class or database class ? I need it because otherwise my query doesn't produces expected result.

[El Forum]

[eluser]Derek Allard[/eluser]
Currently there is no way to add brackets in AR. There are some upcoming changes that may address this for you... do you use the SVN repository at all? If not, check out this page for a quick once over... you'll be able to track it yourself.

[El Forum]

[eluser]Phil Sturgeon[/eluser]
Yea sure you can use brackets. Its not entirely obvious at first, but remember that all AR does is to join strings. So if you have:

Code:

$this->db->where('foo, $var1);
$this->db->where('( bar, $var1);
$this->db->orwhere('blobby = '.$var2.')');

This will create:

WHERE foo = var1 AND (bar = var1 OR blobby = var2)

Of course this is a bit confusing, so you may find it easier to use AR as normal, but put your complex WHERE statements in a single where line. For example...

Code:

$this->select('whatever');
$this->db->join('table', 'table.id = othertable.id');
$this->db->where('WHERE foo = '.$var1.' AND (bar = '.$var1.' OR blobby = '.$var2.')');

[El Forum]

[eluser]Derek Allard[/eluser]
That's really clever pyro... I never thought of that, but I need to warn you that it won't work pending the next update to AR.

Tell you what, what I've done is added 2 new functions into Active Record. They provide a way for a developer to write a raw, untouched where clauses.

Go grab
http://dev.ellislab.com/svn/CodeIgniter/...ve_rec.php

and replace your current system/database/db_active_rec.php file with it.

$this->db->raw_where();

Generates an unfiltered WHERE portion of the query exactly as the developer passes it. Separates multiple calls with AND

Code:

$this->db->raw_where('(grade > 50 AND grade < 75)');
// Produces: AND WHERE (grade > 50 AND grade < 75)

$this->db->raw_or_where();

Generates an unfiltered WHERE portion of the query exactly as the developer passes it. Separates multiple calls with OR

Code:

$this->db->raw_where('(grade > 50 AND grade < 75)');
// Produces: OR WHERE (grade > 50 AND grade < 75)

Note: All values passed through raw_where() and raw_or_where() are not escaped automatically, or otherwise touched. It is the responsibility of the developer to ensure all queries are safe.

[El Forum]

[eluser]CodeIgniter Fan[/eluser]
Thanks Derek & Pyro

[El Forum]

[eluser]Derek Allard[/eluser]
Sorry guys. I'm taking raw_where and raw_or_where out. They duplicated existing functionality better left elsewhere.

Even in the upcoming AR changes, you can get the same functionality with

Code:

$this->db->where('(grade > 50 AND grade < 75)');
$this->db->get('grades');

[El Forum]

[eluser]CodeIgniter Fan[/eluser]
Ok Derek

[El Forum]

[eluser]Khoa[/eluser]
Derek, if what I want to compare is a string (like a LIKE clause), will manually doing it like that make my code more vulnerable? Because as I read on the user guide that things inside db->where, db->or_where...are automatically escaped. So manually doing it means it will not be escaped at all!! Is it right? If yes, how can I achieve both the flexibility of hand writing where clauses and the power that CI provides? Thanks.

BTW, with "escaping" does it mean my code is completely safe? And no need to worry about things like sql injection at all?

[El Forum]

[eluser]Derek Allard[/eluser]
Should be ok, the string is still getting run through "where()" so all the active record escaping remains.

BTW, with “escaping” does it mean my code is completely safe? And no need to worry about things like sql injection at all?

Well, of course there is no such thing as "perfect" security, so I won't go so far as to day that there is no need to worry about sql injection, but to answer your question, the intention of the escaping that happens is to remove the burden of needing to code in their own protection from a developer. So yeah, you should be good. Stay vigilant, always think about security, but you should be good.

[El Forum]

[eluser]Khoa[/eluser]
Oh ye, that's right, it is still considered as a string to the where() function :-). It makes me feel better now because I do have such manual comparison in quite a few places.

I always wish there is such as perfect security feature :-), sth that I can only say: hey, someone submits something, check it for me! Like one of your robots :-P! There are so many websites running out there, and everyone has someway to protect their data, so I think the solution to most of the security holes already exist, just the matter of how they are put together and who has the knowledge to do that so that we do not need to worry about any of those when we make website. But anyway, I trust CI already has very good security features, so dont wanna worry about it for now! :-D

Thanks again Derek.