CODEWITHSUNDEEP
phpmysqlcakephpcakephp-3.0
Yakng
Yakng

Reputation: 135

How to escape mysql database name to prevent sql injection in cakephp3?

I have a requirement where i have to create new mysql database with the name provided by user through form. For now i have allowed only alphanumeric characters for database name.

I think this alphanumeric validation on database name somehow protects me from sql injection but still i want to to prevent sql injection completely. I have tried to use mysql_real_escape_string on user input but it's not escaping if user input is like this new_db_name; DROP DATABASE other_database; --.

So how can i escape user input so that it can be used safely for databse name preventing sql injection? I am using cakephp3 , i have tried following code in cakephp3 which is not escaping user input like new_db_name; DROP DATABASE other_database; --

    $db = mysql_real_escape_string($user_input);
    $rootConnection = ConnectionManager::get('rootUserConnect');

    //i)using query method
    $rootConnection->query("CREATE DATABASE $db CHARACTER SET utf8 COLLATE utf8_general_ci");

    //ii) or using execute method , it's throwing mysql syntax error
    // $rootConnection->execute("CREATE DATABASE :db CHARACTER SET utf8 COLLATE utf8_general_ci",['db' => $db]);

Thanks in advance.

Upvotes: 0

Views: 967

Answers (2)

José Lorenzo Rodríguez
José Lorenzo Rodríguez

Reputation: 9614

You need to use the quoteIdentifier() function in the driver:

$rootConnection = ConnectionManager::get('rootUserConnect');
$db = $rootConnection->driver()->quoteIdentifier($db);
$root->connection->execute(...);

More information about this method: http://api.cakephp.org/3.2/class-Cake.Database.Connection.html#_quoteIdentifier

Upvotes: 1

floriank
floriank

Reputation: 25698

Use the query builder. You effectively ignore the ORM by what you're doing. One of the reasons to use an ORM is to prevent SQL injections. The ORM will take care of sanitizing the query for you.

There are a few cases in which a developer can still cause the possibility of an injection. The manual tells you as well how to prevent that.

Upvotes: 1

Related Questions