I understand that the safest way to protect your website from SQL injection is to use Prepared Statements. But how does someone can go around this if user input is converted to a number?
$id = (int)$_GET['id'];
-
2That this particular instance is secure doesn't mean you should skip prepared statements. Using prepared statements always isn't hard with the right DB class/ORM, and using it all the time means you're a lot less likely to forget to sanitize. It's kinda like wearing a seat belt - you might drive safely 99% of the time, but it's still good to wear.ceejayoz– ceejayoz2018-01-31 18:24:33 +00:00Commented Jan 31, 2018 at 18:24
-
2@ceejayoz So true and well said.Funk Forty Niner– Funk Forty Niner2018-01-31 18:28:55 +00:00Commented Jan 31, 2018 at 18:28
Add a comment
|
1 Answer
But how does someone can go around this if user input is converted to a number?
Simply put, they can't, end of story/hits brick wall. Since it's been casted as an integer, it cannot be modified or manipulated from thereon.
Only you have that power to modify it, if you wanted/had to.
For example:
$string = "String";
$id = (int)$_GET['id'];
echo $new_value = $string . "" . $id;
Producing String123.
A var_dump($new_value); will produce string(9) "String123".
But now, that would be a string, so you didn't want that, but it's just to show you what can be done, after the fact.
Since an SQL injection usually contains characters such as ', - or DELETE or anything else that isn't an integer and passed into by a potential hacker, the statement will be rejected.
file.php?id=123 and modified in the URL such as file.php?id=String'); DROP TABLE USERS; -- will not "pass GO", as it were.
What you could do is to check if what is being passed in the array is indeed an integer.
There are a few functions to check for this.
- https://secure.php.net/manual/en/function.is-int.php
- http://php.net/manual/en/function.ctype-digit.php
- https://php.net/manual/en/function.is-numeric.php
filter_input()with the FILTER_VALIDATE_INT filter
https://secure.php.net/manual/en/filter.filters.validate.php
However, using a prepared statement would also be beneficial. Both the mysqli_ and PDO apis offer this.
- https://php.net/manual/en/mysqli.quickstart.prepared-statements.php
- https://php.net/manual/en/pdo.prepared-statements.php
When using for instance the mysqli_ api and then using something such as:
bind_param("i", $id) (the i for "integer"). Even if someone did manage to pass something other than an integer, mysql will still refuse to let it go through, since it was also casted as an integer through the binded argument.
Yet, if that were bind_param("s", $id) - (the "s" for "string"), then that could be a different ballgame.
PDO has this also, being PDO::PARAM_INT.
With that said; stick to using a prepared statement, regardless.
answered Jan 31, 2018 at 17:50
Funk Forty Niner
74.2k14 gold badges71 silver badges146 bronze badges
5 Comments
Funk Forty Niner
@Rammy I noticed you posted a comment here earlier and when I came to read it, you deleted it and was about to respond to it.
Rammy
I'm sorry, I thought that i'm asking too much. You spend enough time answering my first question. :)
Funk Forty Niner
@Rammy Well.. swing it by me and I'll see what I can do; I have a bit of time on my hands right now but not for too too long.
Rammy
Which one is better to use in the MySQL statement for integer "SELECT * FROM users WHERE id='".$id."'" or "SELECT * FROM users WHERE id=$id" ? I guess first one is treated as a string and the second one as a integer or it doesn't matter and both are good?
Funk Forty Niner
@Rammy Both do the same thing. However, that would be a different story if it were used in a function such as
IN() mysqltutorial.org/sql-in.aspx where and if the id value is indeed an integer and the column type would fail, since it cannot be quoted.