How to escape things within zend literal?

Question

I'm creating an advanced search and wanted to loop through my queries by adding them to an array like so:

private $searchFields = [
    'as_first_name'                  => 'users.first_name like "%VALUE%"',
    'as_last_name'                   => 'users.last_name like "%VALUE%"',
    'as_payment_history_invoice_num' => 'users.user_id = (SELECT user_id from payment_history where payment_history.invoice_number = "VALUE" LIMIT 1)',
    'as_building_num'                => 'property_units.building_number like "%VALUE%"',
    'as_residents_email'             => 'users.email like "%VALUE%"',
    'as_property_name'               => 'property.name like "%VALUE%"',
    'as_phone_num'                   => 'REPLACE(REPLACE(REPLACE(REPLACE(users.phone, " ", ""), "(", ""), ")", ""), "-", "") = "VALUE"',
    'as_unit_num'                    => 'property_units.unit_number = "VALUE"',
    'as_account_status'              => 'user_status.status_name = "VALUE"'
];

so on search I'm doing something like..

if (array_key_exists($key, $this->searchFields)) {

    $form->get($key)->setValue($val);
    $where->NEST->literal(str_replace('VALUE', urldecode($val), $this->searchFields[$key]))->UNNEST;
}

but the issue is I'm not escaping anything there. Not good. How can I use the same structure but also be escaping stuff.

score 6 · Accepted Answer · answered Feb 16 '17 at 08:34

Literal predicate is for the cases when there are no placeholders. You should use Expression predicate instead.

private $searchFields = [
    'as_first_name'                  => 'users.first_name like "?"',
    'as_last_name'                   => 'users.last_name like "?"',
    'as_payment_history_invoice_num' => 'users.user_id = (SELECT user_id from payment_history where payment_history.invoice_number = "?" LIMIT 1)',
    'as_building_num'                => 'property_units.building_number like "?"',
    'as_residents_email'             => 'users.email like "?"',
    'as_property_name'               => 'property.name like "?"',
    'as_phone_num'                   => 'REPLACE(REPLACE(REPLACE(REPLACE(users.phone, " ", ""), "(", ""), ")", ""), "-", "") = "?"',
    'as_unit_num'                    => 'property_units.unit_number = "?"',
    'as_account_status'              => 'user_status.status_name = "?"'
];

zend-form values should already be decoded, so urldecode is not needed

if (array_key_exists($key, $this->searchFields)) {

    $form->get($key)->setValue($val);
    $where->NEST->expression($this->searchFields[$key], $val)->UNNEST;
}

I didn't use zend-db in quite a while, make sure to check that this code is actually producing the query you need.

I think thats closer.. but I'm getting. SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens I'm binding one param per statement so not sure why thats happening. — hamobi, Feb 16 '17 at 18:10
you have to get rid of quotes around the ?.. and you have to add % around the like values themselves. — hamobi, Feb 16 '17 at 18:18
@hamobi I didn't remember placeholders being quoted, makes sense tho. Also, as a side note, placeholders can be in two forms: positional (`?`) and named (`:placeholder_name`). If you use named placeholders, you need to provide associative array for values (second argument), where key matches placeholder name, named placeholder may appear multiple times — Xerkus, Feb 19 '17 at 12:38

score 0 · Answer 2 · answered Feb 16 '17 at 00:13

You should not have to use urldecode; it should have been decoded before you get here. It sounds like NEST might be too fancy for this situation.

foreach (...)
{
    $val = ...;  // Get the raw value from the form field ($_POST[...] or whatever)
    $mval = addslashes($val);
    $sf = $this->searchFields[...];
    $msf = str_replace('VALUE', $mval, $sf);
    ... $msf ...
}

mysqli_real_escape_str would be better than addslashes, but you need to have the mysql connection object; do you have it?

No I don't have access to the db connection.. Need to do this in a zendy way — hamobi, Feb 16 '17 at 00:17

How to escape things within zend literal?

2 Answers2