防止对具有变量（和大）列数的查询进行sql注入

Question

I have a sql query that is generated using php. It returns the surrogate key of any record that has fields matching the search term as well as any record that has related records in other tables matching the search term.

I join the tables into one then use a separate function to retrieve a list of the columns contained in the tables (I want to allow additions to tables without re-writing php code to lower ongoing maintenance).

Then use this code

foreach ($col_array as $cur_col) {
    foreach ($search_terms_array as $term_searching) {
        $qry_string.="UPPER(";
        $qry_string.=$cur_col;
        $qry_string.=") like '%";
        $qry_string.=strtoupper($term_searching);
        $qry_string.="%' or ";
    }
}

To generate the rest of the query string

select tbl_sub_model.sub_model_sk from tbl_sub_model inner join [about 10 other tables]

    where [much code removed] or UPPER(tbl_model.image_id) like '%HONDA%' or
 UPPER(tbl_model.image_id) like '%ACCORD%' or UPPER(tbl_badge.sub_model_sk) like '%HONDA%'
 or UPPER(tbl_badge.sub_model_sk) like '%ACCORD%' or UPPER(tbl_badge.badge) like '%HONDA%'
 or UPPER(tbl_badge.badge) like '%ACCORD%' group by tbl_sub_model.sub_model_sk

It does what I want it to do however it is vulnerable to sql injection. I have been replacing my mysql_* code with pdo to prevent that but how I'm going to secure this one is beyond me.

So my question is, how do I search all these tables in a secure fashion?

Answer 1

Here is a solution that asks the database to uppercase the search terms and also to adorn them with '%' wildcards:

$parameters = array();
$conditions = array();
foreach ($col_array as $cur_col) {
    foreach ($search_terms_array as $term_searching) {
        $conditions[] = "UPPER( $cur_col ) LIKE CONCAT('%', UPPER(?), '%')";
        $parameters[] = $term_searching;
    }
}

$STH = $DBH->prepare('SELECT fields FROM tbl WHERE ' . implode(' OR ', $conditions));
$STH->execute($parameters);

Notes:

1. We let MySQL call UPPER() on the user's search term, rather than having PHP call strtoupper()
   That should limit possible hilarious/confounding mismatched character set issues. All your normalization happens in one place, and as close as possible to the moment of use.
2. CONCAT() is MySQL-specific
   However, as you tagged the question [mysql], that's probably not an issue.
3. This query, like your original query, will defy indexing.