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
– Tim Burch, Commented Mar 11, 2014 at 0:53
Using sprint to compose queries is a really awful substitute for proper SQL placeholders with a system like the Zend framework offers. There's no reason to be using the horribly antiquated mysql_query here. — tadman
– tadman, Commented Mar 11, 2014 at 1:35

binaryNomad · Accepted Answer · 2014-03-11 00:51:20Z

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, 2014 at 0:51

binaryNomad

3262 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

binaryNomad Over a year ago

This was also covered in another post: stackoverflow.com/questions/3666734/php-sprintf-escaping

user2888624 Over a year ago

This works and the SQL returns like it should but for some reason it does not return any records.

binaryNomad Over a year ago

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%%'"

user2888624 Over a year ago

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 Over a year ago

this is working. I was looking at the created date and not the posted date.

Community · Accepted Answer · 2017-05-23 12:09:08Z

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, 2017 at 12:09

CommunityBot

11 silver badge

answered Mar 11, 2014 at 0:51

Nate C-K

5,9812 gold badges32 silver badges46 bronze badges

3 Comments

user2888624 Over a year ago

I am only using this to get by for now. I will be changing whole framework to Zend2 and PDOMYSQL.

Nate C-K Over a year ago

@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!

user2888624 Over a year ago

OK I will correct this by using the PDO_MYSQL. I have it mostly written i just need to implement it.

Collectives™ on Stack Overflow

PHP mysql query using wildcards

2 Answers 2

5 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related