Validation and Detection of SQL Injections in PHP

I'm new to PHP, and not yet familiar with how this works.

1. If I use mysqli_real_escape_string() and parameterize every variable in the SQL qu开发者_JAVA百科ery, can I spare myself doing the validation with is_numeric(), etc.?

2. What is the best way to create a injection detection system? Simply validate the user's input with regex stuff and save it in the database?

Parametrizing your query is enough, you don't need anything else. You'll input the stuff they "inject" als a string, and there is nothing especially wrong with sql in a database... I suspect SO's database is full of SQL ;)

Escaping a value for MySQL simply adds a backslash in front of of the following characters: NULL \n \r \ ' " and 0x1A. It does nothing else.

An "escaped" value isn't magically safe for use in SQL. Escaping prevents special characters (such as quotes) from being misinterpreted. But if you insert an escaped value into an SQL query without surrounding it in quotes, then you've completely missed the point, and you are no more safe than you would have otherwise been.

Remember, the security procedure is quote and escape. You should always use those two together, since neither is safe without the other.

Also, if you can absolutely guarantee that your input value contains none of the above characters, then you also can be certain that your escaped string will always be identical to the unescaped one, and therefore escaping serves no additional purpose under those conditions.

But More Importantly:

Finally we have, in retrospect, realized that SQL was poorly designed from a security perspective, and relying on users properly quote and escape their data is just a really bad idea.

Parameterized queries are the solution, since it safely separates the data from the control structure. Parameterized queries are possible with mysqli by using prepare() followed by bind_param(). No escaping is necessary when using this method, and you can be confident that you are absolutely immune from SQL injection attacks.

Even if you have protected your variables against injection by using a parameterized query or mysql_real_escape_string() (not mysql_escape_string()), you should still validate them on the server side to ensure that they match the expected type of input. That way, if they do not, you can return an error message to your user with a request to retry those form fields.

If you use a parameterized query, such as offered by MySQLi as prepared statements, you needn't also escape the strings. However, if you don't use a parameterized query, it is essential to call mysql_real_escape_string() on every input parameter received by PHP.

This is a good question, and I think one of the best ways to avoid SQL injection is to learn about proper use of prepared statements

These will really help out the security of your application.

The issue with sql injection is the user inserting SQL data into a command and not validating that the data meets your business rules.

Making sure all user data is passed through mysqli_real_escape_string or used prepared statements is the best way to avoid problems.