Variable sanitization X variable filter before insertion in MYSQL

Question

I took a project to make some adjustments and noticed that there were some security loopholes in the database entries, I could not make changes to the querys but I could make adaptations, made the implementations below but not sure if I missed something relevant about the prevention of sql Injection, some suggested improvement?

I have a function to clear the variables:

<?php
if( !function_exists('limpa_sql_injection') ) {
    function limpa_sql_injection($string) {
        $string = str_replace("UNION SELECT"," ",$string);
        $string = str_replace("UNION INSERT"," ",$string);
        $string = str_replace("UNION DELETE"," ",$string);
        $string = str_replace("UNION UPDATE"," ",$string);
        $string = str_replace("uid="," ",$string);
        $string = str_replace("uid ="," ",$string);
        $string = str_replace("pwd="," ",$string);
        $string = str_replace("pwd ="," ",$string);
        $string = str_replace("admin=","",$string);
        $string = str_replace("admin =","",$string);
        $string = str_replace("exec master"," ",$string);
        $string = str_replace("cdmshell"," ",$string);
        $string = str_replace("net user"," ",$string);
        $string = str_replace("or uid"," ",$string);
        $string = str_replace("username=","",$string);
        $string = str_replace("username =","",$string);
        $string = str_replace("ALTER TABLE"," ",$string);
        $string = str_replace("alter table"," ",$string);
        $string = str_replace("--"," ",$string);
        $string = str_replace("OR 1="," ",$string);
        $string = str_replace("or 1=1","",$string);
        $string = str_replace("OR 1=1","",$string);
        $string = str_replace("or 1=","",$string);
        $string = str_ireplace('%3Cscript', '',$string);
        return $string;

    }
}
?>

Treat input of variables:

<?php
if( !function_exists('filtra_var') ){
    function filtra_var($var) {
        $var = trim($var);
        $var = strip_tags($var);
        //$var = htmlspecialchars($var, ENT_QUOTES, 'UTF-8');
        //$var = fgetss($var);
        $var = addslashes($var);
        $var = filter_var($var, FILTER_SANITIZE_STRING);
        $var = limpa_sql_injection($var);
        return $var;
    }
}
?>

And making use of it before moving to Query:

<?php
$var = filtra_var($_POST['var']);
?>

Then I go to Query:

<?php
$sql_insert = "INSERT INTO `tabela` (`var`) VALUES('".mysqli_real_escape_string($conn,$var)."');
?>

It is safe this way or I still run the risk of SQL injections ?

Answer 1

I believe the way to make your code less bulky is to create a array() with the values relating to the changes you want to make with the str_replace(), so whenever you want to add some other value, just add in that array():

function limpa_sql_injection($string){
    $to_replace=array("UNION SELECT","UNION INSERT","UNION DELETE","UNION UPDATE","uid","uid","pwd","pwd","admin","admin","exec master","cdmshell","net user","or uid","username","username ","ALTER TABLE","alter table","--","OR 1=","or 1=1","OR 1=1","or 1=","%3Cscript");
    foreach ($to_replace as $key => $value) {
        $string=str_replace($value,"",$string);
    }
    return $string;
}

Another important thing is to process the data using, for example, regular expressions. The user name must be an alphanumeric nick?

$string=preg_replace("/[^[:alnum:]]/","",$string);

This forces the string to contain only alphanumeric characters, etc.

I advise the use of bindValue() because it ends up being more effective, even more when using the appropriate treatment for each type of input.