CODEWITHSUNDEEP
phpmysqlzend-framework
user2888624
user2888624

Reputation: 15

PHP mysql query using wildcards

I am in search of a way to use wildcards with the following mysql query.

    public function getSetsOnMonth($setsId, $setsDate, $offset, $count)
{
    $sql = sprintf("SELECT * FROM " . $this->_prefix . "media_set AS f
                    INNER JOIN " . $this->_prefix . "media_set_sets_assoc AS fs
                        ON fs.set_id = f.set_id AND fs.sets_id = '%s'
                    WHERE f.posted LIKE '%s'
                        AND  f.is_active = 1
                    ORDER BY f.set_id DESC
                    LIMIT %s, %s",
                    mysql_real_escape_string($setsId),
                    mysql_real_escape_string($setsDate),
                    mysql_real_escape_string($offset),
                    mysql_real_escape_string($count));
    echo $sql; exit;            
    $rs   = mysql_query($sql);
    $rows = array();
    while ($row = mysql_fetch_object($rs)) {
        $rows[] = $row;
    }
    mysql_free_result($rs);
    return new XXX_Model_RecordSet($rows, $this);       
}

What i am looking to do is by month so that would be (where f.posted LIKE '%s') is located I have tried using the % in various ways and it always errors. For instance (where f.posted LIKE '%s %') returns this error (Warning: sprintf(): Too few arguments in..).

I have also tried to use (where f.posted LIKE '$setsDate%') this does not return a sql error, however it makes my query limit the same as the date and the single quote is removed after the % sign in the sql printout.

oddly if i reverse and put the percent sign in front of $setsDate (%setsDate) is cancels out everything after the % and shows everything.

any help would be appreciated.

UPDATED 9:35 EST 03/10

Here is the sql output ('%s %%'): SELECT * FROM media_set AS f INNER JOIN media_set_sets_assoc AS fs ON fs.set_id = f.set_id AND fs.sets_id = '1' WHERE f.posted LIKE '201312 %' AND f.is_active = 1 ORDER BY f.set_id DESC LIMIT 0, 18

Here is SQL output ('%s%%'): WHERE f.posted LIKE '201312%'

Notice no space between.

Upvotes: 0

Views: 351

Answers (2)

Nate C-K
Nate C-K

Reputation: 5932

Your problem is that sprintf treats % as a special character, so it gets confused when you want an actual % character. The solution for that is to use %% when you want the actual % character.

However, please do not insert values into your SQL with sprintf. That is an extremely bad practice and it is responsible for most of the security vulnerabilities in PHP code. Escaping your strings is not good enough. Use parameterized queries instead.

You should use the PDO or mysqli extensions, which support parameterized queries.

There are many articles explaining why you should not splice values into your SQL. Here is one: http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/

The way you should be doing it is discussed here: https://stackoverflow.com/a/60496/219155

Upvotes: 0

binaryNomad
binaryNomad

Reputation: 336

I believe you should be escaping the percentage sign for use in a LIKE statement, as such:

WHERE f.posted LIKE '%s %%'

Upvotes: 1

Related Questions