CODEWITHSUNDEEP
zend-frameworksql-injection
Biggie Mac
Biggie Mac

Reputation: 1357

Quote raw sql in ZEND to avoid sql injection

Long story short, I have an admin section where the user can choose from multiple dropdown lists the tables and fields that must be queries in order to get some values. Therefore, the query in ZEND is performed by concatenating the strings

$query = "SELECT $fieldName1, $fieldName2 from $tableName where $fieldName1 = $value";

How can I escape the above using ZEND approach to avoid sql injection? I tried adding them all as ? and calling quoteinto but it seems this does not work on some of the variables (like table names or field names)

Upvotes: 0

Views: 447

Answers (3)

Your Common Sense
Your Common Sense

Reputation: 157885

In SafeMysql you can make it as simple, as 

$sql  = "SELECT ?n, ?n from ?n where ?n = ?s";
$data = $db->getAll($sql,$fieldName1,$fieldName2, $tableName, $fieldName1, $value);

though I understand that you won't change your ZF to SafeMysql.

Nevertheless, there is one essential thing that is ought to be done manually:
I doubt you want to let users to browse users table or financial table or whatever. So, you have to verify a passed table name against an allowed tables array.

like 

$allowed = ('test1','test2');
if (!in_array($tableName, $allowed)) {
    throw new _403();
}

Upvotes: 0

jmat
jmat

Reputation: 320

Use quoteInto() or Zend_db_Select::where() for the values, and for the table and column names, I would simply strip any non alpha characters and then wrap them in ` quotes prior to using them in your SQL.

Example:

// Strip non alpha and quote
$fieldName1 = '`' .  preg_replace('/[^A-Za-z]/', '', $fieldName1) . '`';
$tableName = '`' . preg_replace('/[^A-Za-z]/', '', $tableName) . '`';
// ....

// Build the SQL using Zend Db Select
$db->select()->from($tableName, array($fieldName1, $fieldName2))
                ->where($fieldName1 . ' = ?', $value);

Upvotes: 0

Tim Fountain
Tim Fountain

Reputation: 33148

ZF has quoteIdentifier() specifically for this purpose:

$query = "SELECT ".$db->quoteIdentifier($fieldName1).","...

In your case you might (also) want to check against a white list of valid column names.

Upvotes: 2

Related Questions