php input test / mysql_real_escape_string / pdo

Question

I'm working on an application that needs some data to be sent to db through a form. I've developed an input test class for having a clean input. This is my code:

class test
{
    public static function inputTest($inputPar)
    {

        $con=mysqli_connect("localhost","****","*****","*******");

        // Check connection
        if (mysqli_connect_errno()) {
            return "Failed to connect to MySQL: " . mysqli_connect_error();
        }

        $text=htmlspecialchars($inputPar);
        $text0=trim($text);
        $text1=stripcslashes($text0);
        $text2= strip_tags($text1);
        $text3 = str_replace("&rsquo;","'", $text2);
        $text4=mysql_real_escape_string($text3);
        mysqli_close($con);

        return $text4;
    }
}

When I try to use it everything works except the last $text4=mysql_real_escape_string($text3); that just sends me back an empty string every time.

I just converted all the application to the new PDO driver (don't mind about the mysqli driver in this class, it is just a quick example to make it work), and I read that some control is now unnecessary. So I'm asking which control is still mandatory for having a good level of security.

The Php version is 5.5.9.

Log every single variable and tell us the output. You don't need mysql_real_escape_string if you're going to use PDO and no, using mysql_ just for testing purpose is not okay because bad practices tend to be built up on bad practices. — Jonast92, Nov 14 '14 at 10:51
input string are $input="sjkaksdkdkdk"; output is: text: sjkaksdkdkdk text0: sjkaksdkdkdk text1: sjkaksdkdkdk text2: sjkaksdkdkdk text3: sjkaksdkdkdk text4: (text4 is just an empty string) — JahStation, Nov 14 '14 at 11:04

score 2 · Answer 1 · edited May 23 '17 at 12:20

You can't use ext/mysql functions with an ext/mysqli connection. You must use one or the other for both the connection and the escaping.

The equivalent PDO method is PDO::quote(), but be aware this adds single-quotes to each end of the string, unlike the escape-string functions of the older mysql extensions.

All this is moot because it's unnecessary to sanitize inputs if you use prepared statements and add variables into queries using query parameters. In fact, you must not escape strings if you pass them as query parameters, because you'll get literal backslashes inserted into your database where you didn't want them.

See lots of good examples and discussion here: How can I prevent SQL-injection in PHP?

All the other "sanitizing" functions you're using are also unnecessary, at least for purposes of SQL injection defense. Those functions are not simply "make it more safer" with a cumulative effect. They each have specific purposes, and it's naive to simply use them all together.

It's like if you have a woodworking project, and you apply waterseal, stain, clearcoat, and three colors of paint onto the same surface. Each of them individually is fine, but using them all together shows you don't know what any of them is for.

You may like to view my presentation SQL Injection Myths and Fallacies, or watch me delivering that presentation to the San Francisco MySQL User's Group: https://www.youtube.com/watch?v=o4dJ7hdA8fs or a free webinar I recorded for Percona: http://www.percona.com/webinars/2012-07-25-sql-injection-myths-and-fallacies

many thanks i will check all the links for seeing if my knowledge increase! — JahStation, Nov 14 '14 at 14:58

php input test / mysql_real_escape_string / pdo

1 Answers1