Easiest Way to Make all inputs sql safe?

[eluser]jplanet[/eluser]
I am wondering if there is a quick way I can apply mysql_real_escape_string to all post variables within a function.

I have used this technique when dealing directly with POST variables, and I wonder if there is a way I can apply it to CI's input->post variables:

Code:

foreach ($_POST as $key => $value) {
    $_POST[$key] = mysql_real_escape_string($value);
  }

[eluser]cahva[/eluser]
If you use Active Record or binding to handle database actions, they are automatically escaped already so you dont have to use mysql_real_escape_string.

[eluser]jplanet[/eluser]
Thanks for your reply, cahva. I don't use active record on some of my bigger queries because it prevents me from cutting/pasting queries to and from my mySQL gui application to test and troubleshoot them...This would also require even more rewriting than having to put the mysql_real_escape_string on every single field...

As work deadlines go, that looks like what I have to do now anyway with no other suggestions, but if anyone else has any ideas, it would come in handy for future applications...

[eluser]cwt137[/eluser]
I would use query bindings. Here is an example from the User Guide:

Code:

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";

$this->db->query($sql, array(3, 'live', 'Rick'));

[eluser]jplanet[/eluser]
Best practices aside, is there some way to write to the input->post variables? Something like:

Code:

foreach ($this->input->post as $key => $value) {
    $this->input->post($key)= mysql_real_escape_string($value);
  }

The reason I am looking for this kind of solution is to avoid having to re-write dozens of insert/update functions using the other suggested methods...(as well as render them impossible to debug in a query editor)...

[eluser]n0xie[/eluser]
[quote author="jplanet" date="1251510225"](as well as render them impossible to debug in a query editor)...[/quote]
You can use $this->db->last_query() or the profiler if you want to see the output of the query. Just as easy to copy paste to a query editor imho...

[eluser]jplanet[/eluser]
[quote author="n0xie" date="1251514903"][quote author="jplanet" date="1251510225"](as well as render them impossible to debug in a query editor)...[/quote]
You can use $this->db->last_query() or the profiler if you want to see the output of the query. Just as easy to copy paste to a query editor imho...[/quote]

Thanks, that is very helpful to knoe...I would definitely apply these techniques to new queries that are written for the front-end of the site, or if the project schedule allots time for optimizing code I will use it to go back and fix the model functions that are already written...meanwhile I will be crazily pasting mysql_real_escape_string all over my controllers! (unless anyone else has any suggestions - perhaps I can even modify the CI Input class? Seems dangerous but could be fun)...

[eluser]Rick Jolly[/eluser]
Just modify your queries to use bindings and be done with it. Anything else is a hack.

[eluser]jplanet[/eluser]
Actually, I just came up with something that saved me hours of unnecessary work. I added a new function to the Input class called postsafe, which is identical to the post function but returns a query-safe string:

Code:

function postsafe($index = '', $xss_clean = FALSE)
    {        
        if ( ! isset($_POST[$index]))
        {
            return FALSE;
        }

        if ($xss_clean === TRUE)
        {
            if (is_array($_POST[$index]))
            {
                foreach($_POST[$index] as $key => $val)
                {                    
                    $_POST[$index][$key] = $this->xss_clean($val);
                }
            }
            else
            {
                return $this->xss_clean($_POST[$index]);
            }
        }

        return mysql_real_escape_string($_POST[$index]);
    }

It took me less than a minute to get the job done, as opposed to the many hours it would take to re-write the queries as so many have suggested...and, oddly, keep suggesting, despite my pleas that it wouldn't work for this situation ;-).

[eluser]n0xie[/eluser]
You could probably just extend the CI_Input class. Take a look here: http://ellislab.com/codeigniter/user-gui...asses.html

edit : never mind you figured it out on your own