1

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?

Arie B.
  • 290
  • 1
  • 10
  • 1
    Escaping wouldn't work because that would be for quotes, quotes aren't for tables/columns. You can make a whitelist of allowed terms and then pass it in. – chris85 Oct 20 '17 at 14:44
  • I guess in my case I should only really be worried about semicolons being inserted? But I was thinking about going the whitelist way as well. – Arie B. Oct 20 '17 at 14:48
  • I guess that you have a list of databases that can be dropped. Can't you just check if the term is in that list? – arilia Oct 23 '17 at 06:19
  • There is a list, but I don't trust that either. – Arie B. Oct 23 '17 at 07:39

1 Answers1

1

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");
hunteke
  • 3,648
  • 1
  • 7
  • 17