使用Medoo清理用户输入

Medoo is a PHP database framework. Does anyone know whether I need to sanitise user input before using the insert() function? On the homepage (http://medoo.in/) under the "Why medoo?" section then it simply says "prevent SQL injection" but I don't know if that means it does it for you or that it's just easier to do so.

Does anyone know? I seems more likely that it does it for you but I'd rather know for sure.

Seems like they are filtering out SQL Injections are you don't have to worry about it. [They have listed it as one of their key features]

A quick look at the code the class is basically a CRUD for multiple database types.

They use the quote() method to escape user input.

Though PHP suggest

If you are using quote() to build SQL statements, you are strongly recommended to use PDO::prepare() to prepare SQL statements with bound parameters instead of using PDO::quote() to interpolate user input into an SQL statement. Prepared statements with bound parameters are not only more portable, more convenient, immune to SQL injection, but are often much faster to execute than interpolated queries, as both the server and client side can cache a compiled form of the query.

Actually, looking at the code, they don't seem to use the PDO prepare approach to constructing their code, which means even with quote, a hack may be possible using weird wide characters. See https://security.stackexchange.com/questions/9908/multibyte-character-exploits-php-mysql

Medoo dropped support of PDO prepare because the quoting process of prepare is not good with serialized array data in current, and with some compatible issues.

However you can use it if you like http://medoo.in/api/pdo

$calories = 150;
$colour = 'red';

$sth = $database->pdo->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');

$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);

$sth->execute();

Version 1.6.1 doesn't seem to escape by default.

$database = new Medoo([
    "database_type" => "mysql",
    "database_name" => "database",
    "server" => "localhost",
    "username" => "user",
    "password" => "1234",
    "charset" => "utf8"
]);


// Original proper query and injection

$table_proper_plain = 'TAB_1';
$table_inject_plain = 'TAB_1" UNION SELECT username, password FROM TAB_2;#';

$database->select($table_proper_plain, ["COL_1","COL_2"]);
// SELECT `COL_1`,`COL_2` FROM `TAB_1`
// valid, returns rows from TAB_1
// works as expected

$database->select($table_inject_plain, ["COL_1","COL_2"]);
// SELECT `COL_1`,`COL_2` FROM `TAB_1` UNION SELECT username, password FROM TAB_2;#"
// valid(!), returns rows from TAB_1 AND TAB_2(!)
// bad, injection successful


// Using method quote on proper and injection query

$table_proper_quote = $database->quote($table_proper_plain);
$table_inject_quote = $database->quote($table_inject_plain);

$database->select($table_proper_quote, ["COL_1","COL_2"]);
// SELECT `COL_1`,`COL_2` FROM "'TAB_1'"
// not valid, error 1146: Table 'database.'TAB_1'' doesn't exist
// bad, quoting broke query

$database->select($table_inject_quote, ["COL_1","COL_2"]);
// SELECT `COL_1`,`COL_2` FROM "'TAB_1\" UNION SELECT username, password FROM TAB_2;#'"
// not valid, error 1146: Table 'database.'TAB_1'' doesn't exist
// good, injection not successful