filter_input and mysqli_real_escape_string for int

问题:

I'm developing a simple PHP database application for internal use but would like to code it to best practices. Some of my pages are receiving integer values from GET requests and I'm just wondering how much validation and sanitation is really required.

Currently I'm using $num = filter_input(INPUT_GET, 'num', FILTER_VALIDATE_INT, $num_options); with specified min and max values. From here I'm exiting with an error message if $num == false

Is it necessary to also use $mysqli->real_escape_string($num);

Currently I am not bothering because I think it's quite hard to do SQL injection using an integer...

Thanks,

Kevin

UPDATE: To clarify the query I'm doing looks like this

$sql = "SELECT employeeID, concat(FirstName, ' ', LastName) as Name FROM employee WHERE employeeID='$num'";

回答1:

Like many other PHP users you are taking escaping wrong. You taking it as a some sort of magic wand which makes some "evil characters" "safe".
This is wrong idea.
though prepared statements can be taken as a sort of such a magic wand, escaping is not a synonym for "SQL injection protection". It is a merely string syntax rule - no more, no less.

Is it necessary to also use $mysqli->real_escape_string($num);

It is irrelevant question.
To escape or not to escape decision have to be bound to SQL, not to the data source or any validations:

• real_escape_string() have to be used for the sql strings, i.e. parts of the query enclosed in quotes. Have to be used unconditionally, despite of whatever previous manipulations.
• For the any other part of the query real_escape_string() being completely useless.

An explanation:
Data validation rules can be changed.
While SQL building rules have to be explicit and unconditional. To make a developer never ask himself a question like this.
In fact, it's completely different matters: data validation and query building. Why keep in mind such details and build the query accordingly? Why not to build the query based on some set of general purpose rules, irrelevant of the data nature at all?

So, to your question again:

• if you are adding your data to the query as is, without quotes, real_escape_string() going to be completely useless in this case, but casting/validation become essential.
• if you are adding your data to the query using prepared statement, real_escape_string() going to be completely useless and even harmful.
• if you are adding your data to the query in quotes - you ought to do real_escape_string() in this case.
• it is also worth to mention that if you are adding your data to the query as a part of SQL language - as an identifier or an SQL keyword - real_escape_string() is completely useless too, as well as prepared statement. Whitelisting is your only friend here

回答2:

I see your using mysqli, your best option for security is to look into Prepared Statements.

PHP mysqli Prepared Statements

It's a bit involved for an example, but the above link has indepth examples.
Once you get the hang of it though, and build your class. It's really only a normal sql query but instead of including your values you use ?

"SELECT * FROM account WHERE username = ? AND password = ?"

and you bind your values to the statement:

array("bradley", "Passw0rd");

The security comes from, as a short answer, is the fact you don't concat the values into the query string yourself. Making it less prone to sql injection.