I know most people say to just use prepared statements, but I have a site with many existent queries and I need to sanitize the variables by the mysqli_real_escape_string() function method.

Also the php manual of mysqli_query() says mysqli_real_escape_string() is an acceptable alternative, so here I am ...

I want to do this type of queries:

$query = sprintf("SELECT * FROM users WHERE user_name = %s", 
                    query_var($user_name, "text"));
$Records = mysqli_query($db, $query) or die(mysqli_error($db));

I want to know below function would work, I am unsure if:

• I should still do the stripslashes() at the start ? An old function I used from Adobe Dreamweaver did this.
• Is it OK to add the quotes like $the_value = "'".$the_value."'"; after the mysqli_real_escape_string() ?
• Does it have any obvious / big flaws ?

I noticed the stripslashes() removes multiple \\\\\\ and replaces it with one, so that migt not work well for general use, e.g when a user submits a text comment or an item description that might contain \\\\, is it generally OK not to use stripslashes() here ?

I am mostly worried about SQL injections, it is OK if submitted data included html tags and so, I deal with that when outputing / printing data.

if(!function_exists('query_var')){
    function query_var($the_value, $the_type="text"){
        
        global $db;
        
        // do I still need this ?
        // $the_value = stripslashes($the_value);
        
        $the_value = mysqli_real_escape_string($db, $the_value);
        
        // do not allow dummy type of variables
        if(!in_array($the_type, array('text', 'int', 'float', 'double'))){
            $the_type='text';
        }
        
        if($the_type=='text'){
            $the_value = "'".$the_value."'";
        } 
        
        if($the_type=='int'){
            $the_value = intval($the_value);
        }
         
        if($the_type == 'float' or $the_type=='double'){
            $the_value = floatval($the_value);
        } 
        
        return $the_value;
        
    }
}

CodePudding user response：

A text string constant in MySQL / MariaDB starts and ends with a single quote ' character. If the text itself contains a quote character, we escape it by doubling it. So the name "O'Leary" looks like this in a SQL statement's text.

SET surname = 'O''Leary'

That's the only rule. If your users feed you data with backslashes or other escaping schemes, you can feed it verbatim to MySql with the kind of text string representation mentioned here.

Don't overthink this. But use carefully debugged escaping functions. Avoid writing your own, because any tiny bug will allow SQL injection.

CodePudding user response：

Looking at the PHP functions documentation, I found some references that made me decide the stripslashes() is not needed in that function.

https://www.php.net/manual/en/security.database.sql-injection.php

Generic functions like addslashes() are useful only in a very specific environment (e.g. MySQL in a single-byte character set with disabled NO_BACKSLASH_ESCAPES) so it is better to avoid them.

https://www.php.net/manual/en/function.addslashes.php

The addslashes() is sometimes incorrectly used to try to prevent SQL Injection. Instead, database-specific escaping functions and/or prepared statements should be used.