Will this be enough to prevent Mysql injection usi

问题:

I know most of the answers will say use PDO/Mysqli but I'm trying to see if I can do it this way then move on to PDO/Mysqli still learning:

Will this function be enough to prevent mysql injection?

function anti_inject($sql)
{

    $sql = preg_replace(sql_regcase("/(from|select|insert|delete|where|drop table|show tables|#|\*|--|\\\\)/"), "", $sql);
    $sql = preg_replace("/[^a-zA-Z0-9]+/", " ", $sql);
    $sql = mysql_real_escape_string($sql);
    $sql = trim($sql);
    $sql = strip_tags($sql);
    $sql = addslashes($sql);
    $sql = strtolower($sql);
    return $sql;
}

Looking for a better replacement for this line $sql = preg_replace(sql_regcase("/(from|select|insert|delete|where|drop table|show tables|#|*|--|\\)/"), "", $sql);

As I do want to check for names that have "from" "select" "insert" gaming tags etc

I've disabled drop table from the mysql user

回答1:

No. That's hopeless.

$sql = preg_replace(sql_regcase("/(from|select|insert|delete|where|drop table|show tables|#|\*|--|\\\\)/"), "", $sql);

This throws away data for no apparent reason

$sql = preg_replace("/[^a-zA-Z0-9]+/", " ", $sql);

This throws away data for no apparent reason

$sql = mysql_real_escape_string($sql;

If you hadn't lost the ), then this is the correct way to convert a piece of data for inserting into an SQL query.

$sql = trim($sql);

This throws away data for no apparent reason

$sql = strip_tags($sql);

This throws away data for no apparent reason

$sql = addslashes($sql);

This escapes data in a way inappropriate for an SQL query. Some data will be double escaped (and therefore broken) because you have already used mysql_real_escape_string.

$sql = strtolower($sql);

This mangles data for no apparent reason.

---

When using the obsolete, deprecated mysql_ library, mysql_real_escape_string is the only escaping function you should use. You then need to take the results and use them appropriately when bashing your string of SQL together.

Don't use mysql_ though. Use PDO and parameterised queries.

回答2:

Strictly speaking, in terms of SQL injection protection this function is awfully useless and unusable at the same time. While it may serve you in some particular case of yours, it cannot be used as a general purpose solution. Yet even for such a case it is awfully redundant.

So, why not to use a solution already proved to be error-proof for all the (covered) cases - PDO prepared statements?