2

The best way to avoid SQL injection for defined value type such as numbers, is to validate the value; since it is easier to do so comparing with mysqli preparation. In PHP, we can do this by. 

1. if(!is_numeric($value)) {$value=0;}
2. $value=floatval($value);
3. $value=intval($value);
4. $value=$value * 1;

What is the most reliable one? or a better idea?

UPDATE: Although I have stated in the original question, most of folks emphasized the usefulness of parameterized queries. Definitely, it is the most efficient way to avoid SQL injection. But when we can simply validate an integer numeric value; IMHO, there is no need to parametrization.

Improve this question
4
  • 3
    The best way to avoid SQL injection is to use parameterized SQL statements. Commented Mar 12, 2012 at 13:19
  • 1
    $value=intval($value); Or (int)$value; Commented Mar 12, 2012 at 13:19
  • 1
    Yes if you make everything right, there is no need for parametrization, and parametrization is not always possible as well. However you can choose any tool that does the job, is_numeric and floatval is not part of it if you look for integer values (!). Commented Mar 12, 2012 at 13:28
  • If string might contain leading whitespace that you wish to ignore, use $value = (int)trim($value); Commented Jul 17, 2020 at 20:23

6 Answers 6

Reset to default
5

For integers and floats you can use this if you don't want to do a parameterised query.

$clean_number = (int)$value;

$clean_number = (float)$value;

These are actually casting the value as int and float, this is faster than intval() and floatval() for example because it does not suffer the function overhead.

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

7 Comments

@Ali there is actually nothing nice in function overheads. And nothing "faster" as well.
Benchmark of (int) vs intval(): stackoverflow.com/questions/239136/…
@YourCommonSense Ironically, your comment doesn't make sense, but thanks for the downvote. Tests show the cast is faster than intval().
These tests make no sense. They are a light year far away from the real life. Keep you playing in the imaginary sandbox.
I haven't said that intval($num) is faster than (int)$num. They are equal. Take whatever real life script which is using int casting, create 2 version of it, (int) and intval() one, run apache benchmark on both versions. Try to find the difference.
|
4

I prefer to use the filter extension:

$id = filter_var($id, FILTER_SANITIZE_NUMBER_INT);

You should definitively go for parameterized queries.

Improve this answer

1 Comment

Its worth mentioning pro/con of using sanitize filter. Pro: If there is a number in the input, it will find it. Con: Is that really what you want given a garbage string? - the more restricted behaviour of (int) (or intval()) is often more appropriate - unusual strings simply become 0. HOWEVER, AFAIK, (int) doesn't trim whitespace, so the alternative to filter, when don't want to strip all garbage, probably should be (int)trim($value);
2

You can use intval() to coerce a value to integer. The most reliable way to avoid SQL injection is to use parameterised queries.

Improve this answer

Comments

2 
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$sth = $dbh->prepare('SELECT * FROM table WHERE id = :id');
$sth->bindParam(':id', $id, PDO::PARAM_INT); 
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_ASSOC);
Improve this answer

4 Comments

Yes. Because you don't have dozens of different filtering functions to know. You create your SQL request then give it its params.
@Arkh it is not about different filtering functions. it's about this very code. Which is a mile far away from being easy.
@Arkh if you want to talk of "different filtering functions", there are "dozen" different binding types as well.
The filtering part of this code is the prepare / bindparam lines. All other things are connecting to the db and getting the request result which you'll have to do even with the mysql functions and (int) casting.
1

If you want a seasoned advise, you have to change your mind. Completely.

The thing that being your main concern, in fact, is the most negligible thing in the world. You may use one way or another with not a slightest difference. There is no "most reliable" way. That's just several ways of doing the same.

On the other hand, "there is no need to parametrization" is a grave delusion.
Parameterized queries can do any good only if used explicitly, throughout whole site, with no exceptions. One exception can spoil all the defense.

Not to mention that parameterized query can make your life much, much easier.
Prepared statements are not such an ugly code that propagated on this site by some enthusiasts. It's actually quick and neat way of writing safe code.
Say, the code that took cetver a dozen lines can be done in just one:

$data = $db->getAll("SELECT * FROM table WHERE id = :placeholder:",$id);

and be perfectly safe
without ugly manual binding.
without error-prone manual casting.

Another example to show you the power of placeholders 

$sql = "SELECT * FROM table WHERE tstamp BETWEEN ?i AND ?i AND flag=?s AND IN in (?a)";
$data = $db->getAll($sql,$min,$max,$flag,$array_of_ids);

Two lines.

I am not too good with PDO but it would be like a dozen lines of code even without connect

$in  = implode(',', array_fill(0, count($array_of_ids), '?'));
$sql = "SELECT * FROM table WHERE tstamp BETWEEN ? AND ? AND flag=? AND id IN ($in)"
$sth = $dbh->prepare($sql);
$stmt->bindValue(1, $min);
$stmt->bindValue(2, $max);
$stmt->bindValue(3, $flag);
foreach ($array_of_ids as $i => $id) {
  $stmt->bindValue(($i+4), $id);
}
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_ASSOC);

And comparable amount with your current manual casting.

This is actually the power of programming.
One can write a program to do all the dirty job for them.
An approach almost never seen on this site.

Sapienti sat

Improve this answer

13 Comments

very interesting approach to make prepared statement a constant culture of coding. I really appreciate your way to use prepare statement in one line code; but I wonder how to use it in PHP mysqli?
One have to invent a type-hinted placeholder for this. the query actually have to be like "SELECT * FROM table WHERE id = ?i", where ?i is a placeholder and i is a type. Some parsing have to be done to get the placeholders and their types from the query. After that all data can be easily binded/substituted according to the placeholder type.
Incidentally, note that the code you've replaced from cetver's example only replaces four lines, not 12. You didn't bother writing the other code.
@sarnold well spotted! That's the problem with answers on this site. They never encourage good code styling or a slightest abstraction, never show a better approach! Face the reality, look at the questions - the codes always contains the same ugly raw API function calls. Even connection code seldom separated! And it's real codes right from the production. You all have an excuse for yourself - "it's just a demo!" but you have no idea that this "demo" always copy/pasted and used as is. So, it's 13, not 4.
@sarnold here is a dozen for your pleasure ;)
|
-1

You can use this function, mysql_real_escape_string() to espace special characters, but what you are doing its fine to protect an sqli attack.

Improve this answer

2 Comments

'mysql_real_escape_string()' is not reliable at all. This is the reason that parametrized mysqli is actually needed.
mysql_real_escape_string being useless for the numbers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.