Prevent SQL injections in PHP without parameterized queries?

Question

I know this topic was insured to death, but I would like to receive community feedback regarding security in our web application.

We have a standard LAMP stack web application that contains a large number of database queries that are executed using mysqli_query . These requests are not parameterized at the moment, but there is a naive screening of inputs using addslashes .

I was given the task of making this system more secure, since we will pass penetration testing very quickly. The forces described above know that parameterized queries are a way to make the system more secure, but they don’t want to invest time and effort into rewriting all the queries in the application, as well as changing the structure that we have to do for everyone to work correctly.

So basically, I ask, what are my options?

I ran mysqli_real_escape_string on inputs. I installed a filter that does not allow the transmission of words such as SELECT, WHERE, UNION, in which I think this makes it more secure. I know that mysqli_query allows only one query to be run immediately so that there is some protection (from concatenating updates to the end of the selection).

Do I have any other options?

Edit: I should probably add that if someone can provide an example of an attack that is absolutely inevitable without parameterized queries, which will also be useful. We have a query that looks like this:

 SELECT pl.created p.LoginName, pl.username_entered, pl.ip_address FROM loginattempts pl LEFT JOIN people p ON p.PersonnelId = pl.personnel_id WHERE p.personnelid = $id AND pl.created > $date1 AND pl.created < $date2 

I replaced the UNION query with the object $ id UNION SELECT * FROM p WHERE 1 = 1 , and I can prevent this by not allowing SELECT / UNION, but then I am sure that there are many other types of attacks that I cannot think of. Can anyone suggest a few more?

Update

I convinced the forces that are above me that we need to rewrite the queries for parameterized statements. According to their estimates, it will take several months, but it needs to be done. To win. I think?

Update2

Unfortunately, I was not able to convince that we needed to rewrite all our queries into parameterized ones. The strategy we came up with is to test each entry as follows:

If the user set the input is_int, then it should be discarded. The same goes for real numbers. Run mysqli_real_escape_string over character data. Change all parameters in queries for quotation marks ie

 WHERE staffName = ' . $blah . ' 

According to this answer, we are 100% safe, since we do not change the character set at any time, and we use PHP5.5 with the latin1 character set for the whole time.

Update 3

This question was marked as a duplicate, however, in my opinion, the issue is still not being respected. According to update # 2, we found some conviction that the mysqli_real_escape string function can prevent attacks and is apparently “100% safe”. Since then, no good counter argument has been received (i.e. Demonstration of an attack that can defeat it if used correctly).

Answer 1

• check each individual user input for a data type and where regular expressions are applicable (golden rule: never EVER trust user input)
• use prepared statements
• seriously: prepared statements :)

this is a lot of work, especially if your application is in poor condition (as if it were your case), but this is the best way to have a decent level of security

another way (which I recommend) may be a virtual fix using mod_security or WAF to filter implementation attempts, but first of all: try to write reliable applications (a virtual fix may seem like a lazy way to fix the situation, but it actually requires a lot of work and testing) , and it should only be used on top of already strong application code)

Answer 2

Do I have any other options?

No. It was proved that no external measure, like the ones you tried to implement, provided any help. Your site is still vulnerable.

I ran mysqli_real_escape_string on inputs

Congratulations, you just reinvented the notorious magic_quotes function, which turned out to be useless and is now excluded from the language.

JFYI, mysqli_real_escape_string has nothing to do with SQL injections.

Also, combining it with an existing addslashes() call will ruin your data by doubling the number of slashes in it.

I installed a filter that I think makes it safer.

This is not true. SQL injection is not about adding some words.

In addition, this approach is called the “black list”, it turned out to be almost unreliable. The blacklist is essentially incomplete, no matter how many “offers” you can get.

I know that mysqli_query allows you to run a single query right away so that there is some security.

No. SQL injection is not related to adding another query.

---

Why did I close this question as a duplicate of "How can I prevent SQL injection in PHP?"

Since these issues are mutually exclusive and cannot coexist on one site.

If we agree that the only correct answer is to use prepared instructions, then the question asks: “How can I protect without using ready-made statements”, it makes little sense.

At the same time, if the OP manages to force us to give the positive answer they desperately want, it will make another question obsolete. Why use prepared statements if everything is okay with them?

Also, this particular question is too localized. He does not seek insight, but justification. Only an apology to anyone but PP. This is an excuse that allows them to take an approach that has proven unsafe. Although this depends on them, it makes the issue completely useless to the community.

Answer 3

Use this php function before adding a query to your database mysql_real_escape_string ($ unsafe_variable);