Which is better, using intval() or sprintf('%d')?

Question

I am looking for good answer which is best to use between intval() and sprintf('%d') especially on sanitizing unwanted characters?

$offset = '12';
$max = '100';

function getOffsetLimit($offset, $max = 100) 
{
   $offset = intval($offset);
   $max = intval($max);

   $sql = " LIMIT $offset, $max";
   return $sql
}

or

function getOffsetLimit($offset, $max = 100) 
{
   $sql = sprintf(" LIMIT %d, %d", $offset, $max);
   return $sql
}

--

Thanks,

camsy

Don't do either of these. Use [**prepared statements**](https://secure.php.net/manual/en/pdo.prepare.php) with [**bound parameters**](https://secure.php.net/manual/en/pdostatement.bindparam.php). See [**this page**](https://phptherightway.com/#databases) and [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) for some good examples. — Alex Howansky, Jan 24 '19 at 20:21
Neither: [LIMIT keyword on MySQL with prepared statement](https://stackoverflow.com/questions/10014147/limit-keyword-on-mysql-with-prepared-statement) — Dharman, Jan 24 '19 at 20:22
Possible duplicate of [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — , Jan 24 '19 at 20:22
@Andrew - Using regex for this would be extreme overkill. Just because you _can_ do something with regex doesn't mean it's a good idea. `LIMIT` only allows integers which you can handle very well without regex. However, as others have mentioned, all that is the wrong path either way. — M. Eriksson, Jan 24 '19 at 20:28
hello, i am dealing with legacy codes as of the moment. assuming i cannot use prepared statement, give the sample codes above, which one is better? thanks. — camilox, Jan 24 '19 at 20:28
Wouldn't this then be the perfect time to refactor the code so you can use it? — M. Eriksson, Jan 24 '19 at 20:31
You should put every effort into upgrading from PHP4 (assuming this is the reason you can't use prepared statements) and moving away from concatenated SQL queries. — Dharman, Jan 24 '19 at 20:33
would take ages to do it... the whole system using legacy mysql_query() functions... estimated will take 5 years to do that and will kill the business. somehow, we are on the process of refactoring, but we are under resource. — camilox, Jan 24 '19 at 20:34
@camsy: your first sample is safe for integer escaping. You can also try `$number = (int) $string`, it's an alias for `intval` function. — user1597430, Jan 24 '19 at 20:41
point is, casting user input to integer bad idea, give error and make them put in an integer instead — Andrew, Jan 24 '19 at 20:53

score 4 · Answer 1 · answered Jan 24 '19 at 20:35

4

Neither is better. The proper solution would be to use prepared statements with bound parameters. Note the third $data_type argument for PDOStatement::bindParam() -- that allows you to filter integers.

If you absolutely insist on building SQL with concatenated strings, then I'd use filter_var() with either FILTER_VALIDATE_INT (if you want to abort when the values aren't integers) or FILTER_SANITIZE_NUMBER_INT (if you want to convert the value to a reasonable integer-like facsimile.)

answered Jan 24 '19 at 20:35

Alex Howansky

50,515
8
78
98

yeah, "-5+3-8+7-" or "------++++++" is amazing _reasonable integer-like facsimile_ – user1597430 Jan 24 '19 at 21:04
Neither `intval()` nor `printf()` works for that case either, which is why you shouldn't use any of them. – Alex Howansky Jan 24 '19 at 21:13
Note also that `filter_var()` supports arbitrary min/max values, allowing you to limit to positives -- which neither `intval()` nor `printf()` can do. – Alex Howansky Jan 24 '19 at 21:17

score 2 · Answer 2 · answered Jan 24 '19 at 20:22

2

They do opposite things:

intval() converts a string to an integer.
sprintf('%d') effectively converts an integer to a string.

That aside, you shouldn't use either function when dealing with SQL queries. As others have mentioned, you should be using prepared statements.

answered Jan 24 '19 at 20:22

Jonathon Reinhart

132,704
33
254
328

as of the moment, i am dealing with legacy and created a driver to handle between prepared and non-prepared statements. assuming i cannot use prepared statement, on my example, which one is better? – camilox Jan 24 '19 at 20:26
1

i don't think this is the answer.. i am dealing with legacy codes and all are still using mysql_query(). – camilox Jan 24 '19 at 20:32

Online Sid · Accepted Answer · 2019-01-27T22:43:29.890

Since LIMIT and OFFSET don't work with prepared statements, the next best method is to use filter_var() with FILTER_VALIDATE_INT. I think for LIMIT and OFFSET, FILTER_SANITIZE_NUMBER_INT should not be used.

Note that you can't have OFFSET without LIMIT in MySQL and the code below is for MySQL.

    function getLimitOffsetStatement($limit=null, $offset=null) {
        $sql = '';

        $filterOptions = ['options'=>['min_range' => 0]];
        if (filter_var($limit, FILTER_VALIDATE_INT, $filterOptions)) {
            $sql .= " LIMIT $limit";
            if (filter_var($offset, FILTER_VALIDATE_INT, $filterOptions)) {
                $sql .= " OFFSET $offset";
            }
        }

        return $sql;
    }

this is an awesome approach as well... thanks. – camilox Jan 24 '19 at 22:19 — camilox, Jan 24 '19 at 22:19

score 0 · Answer 4 · answered Jan 24 '19 at 22:58

If you are working with legacy code, then something as simple as this will work....

function build_sql_limit ( $total, $offset, $max = 100 )
{
    if ( ctype_digit ( $total ) )
    {
        $total = $total > $max ? $max : $total;
    }
    else
    {
        $total = $max;
    }

    if ( ! ctype_digit ( $offset ) )
    {
        $offset = NULL;
    }

    return ' LIMIT ' . ( is_null ( $offset ) ? $total : $offset . ', ' . $total );
}

Which is better, using intval() or sprintf('%d')?

4 Answers4