CodeIgniter Forums
Database Query Filter - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Development (https://forum.codeigniter.com/forum-6.html)
+--- Forum: CodeIgniter 2.x (https://forum.codeigniter.com/forum-18.html)
+--- Thread: Database Query Filter (/thread-63407.html)



Database Query Filter - smshr88 - 10-27-2015

Is there any way to filter final query. I want to filter some keyword. like
UNION
CAST
Information_scheme

I don't want to allow these keywords in the query either plain query or using active record. How can I filter these keyword without any modification in system folder.

Please advice me.


RE: Database Query Filter - pdthinh - 10-27-2015

(10-27-2015, 12:19 AM)[email protected] Wrote: Is there any way to filter final query. I want to filter some keyword. like
 UNION
 CAST
 Information_scheme

I don't want to allow these keywords in the query either plain query or using active record. How can I filter these keyword without any modification in system folder.

Please advice me.

You can use $this->db->get_compiled_select() to get the final query and filter it, then use $this->db->query($filtered_query) to run it.
Read more http://localhost:8080/ci/public/user_guide/database/query_builder.html#CI_DB_query_builder::get_compiled_select


RE: Database Query Filter - Kirkja - 10-27-2015

Perhaps you should consider filtering those out before you reach the final query.  In more complex queries, case and union are both commonly used to construct SQL statements.  Given that, you could use str_ireplace (case insensitive) to replace target strings with other strings like an empty.   So your searchIn variable could be either strings or arrays of strings which makes things nice.  I used a double underscore as the replace value so you could see the result easier.  You most likely want to just clip the find words out with a simple empty character.

PHP Code:
$find = array('Case'
'UNION'
'Information_scheme'
);

$searchInArray  = array('This is the union'
'that case was not mine'
'nor was the information_scheme ready'
);

$searchInString 'This is the union that case was not mine nor was the information_scheme ready';

$replaceWithString '__';

$resultA str_ireplace($find$replaceWithString$searchInArray);
$resultB str_ireplace($find$replaceWithString$searchInString);


// results will display as follows
echo "resulA:" print_r($resultAtrue);
resultA: Array ( 
[
0] => This is the __ 
[1] => that __ was not mine 
[2] => nor was the __ ready 


echo 
"resultB:" print_r($resultBtrue);
resultBThis is the __ that __ was not mine nor was the __ ready 

str_ireplace takes arrays or strings as arguments, but gives different results depending. If your searchIn variable is a string that works just fine. I gave both examples above.  You will need to plan how you build queries.  Dynamic queries can do amazing things, if crafted correctly.