CODEWITHSUNDEEP
phpmysqlcakephp-3.0
Arie B.
Arie B.

Reputation: 290

When you absolutely have to manually escape SQL in CakePHP 3.4.7

So I want to do the following SQL query in CakePHP 3.4.7:

DROP DATABASE $databasename

I have found that this doesn't work:

$conn->execute("DROP DATABASE :databasename", ['databasename' => $databasename]);

... because MySQL doesn't accept database and table names as parameters in prepared statements ( see Can PHP PDO Statements accept the table or column name as parameter? )

I cannot use CakePHP's Sanitize class because it has been deprecated in CakePHP 3 ( https://book.cakephp.org/3.0/en/appendices/3-0-migration-guide.html#sanitize )

I cannot use mysqli::real_escape_string because AFAIK there is no mysqli object...

Does anybody know any alternatives?

Upvotes: 1

Views: 314

Answers (1)

hunteke
hunteke

Reputation: 3716

It's a bummer when you can't rely on someone's else code for security, because let's face it, the vast majority of folks don't do security well. Better to rely on those libraries!

But, in cases where you can't, such as perhaps this, consider a small whitelist of possibilities for the content you expect. Think about what you are expecting to see, and enforce that. In my mind, I would expect a database name to consist solely of alphabetic letters and perhaps underscores. That obviously will not cover all possible table name values (for example, databases names can have numbers too), but it is your situation that dictates what matters.

With that in mind, a possible sanitization routine might be a simple regex that enforces that the passed name has no whitespace, begins with a letter, perhaps has more letters or underscores after that, and can be no longer than 16 total characters long:

if ( ! preg_match('/^[A-z][A-z_]{0,15}$/', $databasename) ) {
    # database name does not pass the project standards of alpha
    # letters and underscores only.  Likely crack attempt.
    die('No way, Joker!');   # or something more appropriate.
}

It's not the completeness offered by the DB drivers, but it's simple, easy to read, and will work for >90% of all database names out there. With the above sanity check passed, then you can write the generally-considered-unsafe-method-that-is-safe-in-this-case SQL string directly:

$conn->execute("DROP DATABASE $databasename");

Upvotes: 0

Related Questions