php - validating user input using regex to prevent an sql injection

Solution:

I will begin by saying that you absolutely should be using PHP Prepared Statements here. Do not try to handle SQL injection yourself, and besides this problem was solved a long time ago.

Your pattern might block certain types of SQL injection. For example, let's say you had the following SQL query:

SELECT col1, col2 FROM some_table WHERE col = ?;
Copy code

Your regex pattern would prevent someone from injecting'value'; DELETE FROM some_table into the query. This is because your regex pattern doesn't allow for semicolon.

However, there are other types of injection attacks which don't involve chaining on additional (malicious) statement. Union attacks can also happen, and your current regex does allow for this. Consider injecting the following fragment:

'value' UNION ALL SELECT username, password FROM users
Copy code

This would give the following full SQL query:

SELECT col1, col2 FROM some_table WHERE col = 'value'
UNION ALL
SELECT username, password FROM users;
Copy code

While it would probably be unlikely that the attacker would be able to pull this off, it could happen, and if it did, the attacker could get every username and password from a totally different user table.

Use prepared statements and forget about handling this problem yourself.