PHP SQL Sanitation VS Prepared Statements

Question

Quick one.

I'm in the process of migrating an old web application that uses mysql to mysqli. I used to protect against SQL injection with a custom sanitation function I wrote:

    function sani($text=""){
    if (!is_array($text)) { 
        $text = str_replace("<", "&lt;", $text); 
        $text = str_replace(">", "&gt;", $text); 
        $text = str_replace("\"", "&quot;", $text); 
        $text = str_replace("'", "&#039;", $text); 
        return $text; 
    } 
}

They way I used to use this:

mysql_query("SELECT * FROM `table` WHERE `username` = '" . $sani($userinput) . "'");

Basically all it does is change symbols that can be used for injection into html encoding. It has worked fine up until now, but since i'm migrating to mysqli, I wanted to know if prepared statements would be more secure than this function.

Also, I have read a lot about the speed differences between prepared and unprepared statements, is it really that noticeable? I do around a hundred queries a second, so I doubt I would be affected very much?

Thanks!

Answer 1

Yes, prepared statements would certainly be more secure than this function, and they have the added benefit of not having to decode your data when you get it back from your database, too. By the way, even for the old mysql library, you really should rely on mysql_real_escape_string rather than your custom-built sanitation function :)

Prepared statements can be much faster than unprepared statements, and in a typical usage situation, you'll benefit from this even if you're "just" doing 100 queries/second.

Answer 2

The security of prepared statements originate in fact, that in prepared statements the query and data are sent separately. This is why it becomes impossible to perform 1^st type of SQL injection (the 2^nd type (or indirect) injections are ones that are caused if the query is concatenated from data in database).

The problem with your "protective function" is that it does not cover all cases. If you care to learn more about the issue, you could read slide from Slides From Recent Presentations on SQL Injection or slides on SQL injection: Not only AND 1=1.