Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Potential dangers of using unprepared SQL queries when not processing user input?

Everyone knows or should know parameterized queries help to protect against SQL injection. All of the tutorials and documentation I have seen have revolved around using prepared SQL queries to process form input. But what about when there isn't any form input? I.e. a subsequent query after a user has been logged in such as $stmt = "SELECT theme_preference FROM users WHERE user_id = '1234'"; $query = mysqli_query($conn, $stmt);

Is there any possible way an attacker could exploit this? (Let's say I'm using PHP).

like image 745
brad Avatar asked Nov 19 '16 18:11

brad


People also ask

What is querying unnecessary data in SQL Server?

Querying unnecessary data not only leads to poor performance for the person initiating the call, but can also effect other processes with all the extra processing/CPU, network traffic and database IO that is needed to execute the query. Read other tips on performance tuning. Read more on monitoring queries in SQL Server.

Is unnecessary processing hurting your SQL Server performance?

Performing unnecessary processing during query execution can sometimes prove to be one of the bigger culprits when it comes to the overall performance of your SQL Server database instance. Far too many times I've seen queries that do "SELECT *" when they really only need one or two columns from the result set.

What are the disadvantages of SP_ExecuteSQL in SQL Server?

It is vulnerable to SQL injection which could hamper the security a lot. It is very complex in nature as the query plan is built on the fly. It is difficult to understand how the query is going to form. If sp_executesql is not used for calling the procedure, then the execution plan cannot be reused.

Why is MY SQL query so slow?

Poorly-Written SQL Because SQL is declarative, you can write the same query in many ways to receive the same results. These differences in how queries are written can affect performance negatively. There are two ways you can rewrite queries to improve performance:


1 Answers

The question is not whether the source of the data written in a SQL query is a http form. It's not even if it's from the current request.

The question is whether you trust the source of the data. And that may be a complex question.

You obviously do not trust something that comes from the current request. You also don't trust something that may have come from an earlier request, like for examples fields in a database that are modified by request data. But you also may or may not trust other fields in your database. For example you have IT ops staff, or DB admins. Do you trust them to not inject some kind of an XSS or secondary SQLi attack into a database field to steal user credit card data, which is stored in an audited table, so they cannot just go in and dump it without being noticed? If they injected javascript or a clever SQLi in the right place in a table that is not audited, they may steal credit card info by exploiting the vulnerable application, then change it back and remove all traces.

Also an application may have different sources for data, other systems may for example upload files (say XML) on APIs, data from those will be processed, some of it will eventually make it to the UI or used in SQL queries. If you trust those sources, you may choose to not implement protection against SQLi or XSS. But why would you, when it is easy? Multiple layers of defenses is always better than walking on thin ice.

So in short, the question is trust. If you absolutely trust the source of the data (like for example because it's static, hard-coded, or for some other reason), that's fine to use it directly in queries. But in case of SQL injection, using it properly (ie. in parameters) is so easy that you should just do that.

Also consider future changes. You are writing it in a SQL string without parameters because you know that it's safe now. Months pass, somebody adds a new feature, modifies your query, adds a few more parameters, one is from the request. But the pattern was already there, he will probably just copy-paste and go with the pattern - and your application is vulnerable.

My final point is static security scanners, those that look at your source code. Pretty much all of those will flag your code for SQLi if a variable is included in the query string itself without using parameters. That may of course be a false positive, but I doubt you want to bother with those findings, when you can avoid them in the first place.

So sometimes it's not just about the technical exploitability, there are other aspects too.

like image 157
Gabor Lengyel Avatar answered Sep 29 '22 05:09

Gabor Lengyel