PHP mysql query using wildcards

Question

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.

Per the PHP manual: "[the mysql] extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used." — Tim Burch, Mar 11 '14 at 00:53
Using `sprint` to compose queries is a really awful substitute for proper SQL placeholders with a system like the [Zend framework offers](http://framework.zend.com/manual/1.11/en/zend.db.statement.html). There's no reason to be using the horribly antiquated `mysql_query` here. — tadman, Mar 11 '14 at 01:35

score 1 · Accepted Answer · answered Mar 11 '14 at 00:51

1

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

WHERE f.posted LIKE '%s %%'

answered Mar 11 '14 at 00:51

binaryNomad

336
2
6

This was also covered in another post: http://stackoverflow.com/questions/3666734/php-sprintf-escaping – binaryNomad Mar 11 '14 at 00:52
This works and the SQL returns like it should but for some reason it does not return any records. – user2888624 Mar 11 '14 at 00:59
Can you please add the result of the line "echo $sql;". Just a guess but my LIKE statement had a space between %s and the double % - that may cause issues. You should be doing: "LIKE '%s%%'" – binaryNomad Mar 11 '14 at 01:06
Results are above. Now with '%s%%' it returns all of 201312 like it should but for some reason i am getting 201311 as well. – user2888624 Mar 11 '14 at 01:41
this is working. I was looking at the created date and not the posted date. – user2888624 Mar 11 '14 at 01:44

score 0 · Answer 2 · edited May 23 '17 at 12:09

0

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

edited May 23 '17 at 12:09

Community

1
1

answered Mar 11 '14 at 00:51

Nate C-K

5,744
2
29
45

I am only using this to get by for now. I will be changing whole framework to Zend2 and PDOMYSQL. – user2888624 Mar 11 '14 at 00:58
@user2888624: Every time you post code online that uses this technique you risk giving some newbie the idea that it's OK to do things this way. It is not OK. The mysql extension isn't even supposed to be in use anymore! – Nate C-K Mar 11 '14 at 01:02
OK I will correct this by using the PDO_MYSQL. I have it mostly written i just need to implement it. – user2888624 Mar 11 '14 at 01:05

PHP mysql query using wildcards

2 Answers2