Take the tour ×

I understand the concept of SQL injections, what they are, why they're bad and how to prevent them via such methods as parameterized queries and breaking out of queries, but what happens if you use just a straight query string in code, in a place where there's no user input.

For example, if you stored something about a user when they logged on, and it was done automatically and without the user being aware or doing anything, perhaps something like;

UPDATE tblUser SET lastLogged = blahblah WHERE userID = blahblah2;

Would this cause any sort of threats of worries in the mind of a developer?

My main guess would be that, if a hacker broke into a program or website, perhaps he could use this information further on other attacks? Are queries / strings easily obfuscated?

Cheers in advance.

edit: This is purely for theory, so try to refrain from sayings like "If you're going to do a job, do it properly" and all that.

share|improve this question
add comment

4 Answers

up vote 4 down vote accepted

When using parameters with your queries the database engine will recognize the query as being the same, even though the parameters have changed, thus allowing it to use a cached execution plan, which will lead to better performance. So, yes, always use parameters.

With regards to security there are no real issues with the update statement, as outlined above, unless of course some of the input can be tampered with by users.

share|improve this answer
Interesting, i didn't realise that parameters added performance too, in respect to caching information. Thanks : ) –  Shane.C Sep 18 '12 at 9:19
A cached query plan avoids the time it takes to recompile the query. But the cached plan is typically not optimal for all values of the parameters (Google for "parameter sniffing".) The statement that parameters lead to better performance is more false than true. –  Andomar Sep 18 '12 at 9:22
add comment

If you're going to do...

By allowing the program to execute arbitrary SQL like the update query defined above, if your program is ever compromised, then you can no longer rely on the integrity of your data.

By using a stored procedure (eg SetUserLastLoggedInDate) and denying the permission to execute arbitrary SQL against your database, you can mitigate the potential damage in the event of a compromise.

share|improve this answer
+1 for refraining! Also that's a good idea, using stored procedures. –  Shane.C Sep 18 '12 at 9:22
add comment

I wouldn't worry from a security point of view, as long as none of your blahblahs are under user control. But I'd still use a parameterized query from an efficiency point of view.

Presumably you have more than one user to worry about, so the query would run for different users. Parameterizing the statement allows the DBMS to do more efficient caching.

share|improve this answer
add comment

It's not parameterless queries that are the problem: it's string concatenation. So if you do:

myQuery = "UPDATE tblUser SET lastLogged = blahblah WHERE userID = " + blahblah2

you're asking for trouble. Can you be absolutely certain that blahblah2 hasn't come from an external source? Normally parameters come passed into methods from other methods and the real source is somewhere many classes away. Other developers can change those classes, and the source, without knowing the consequences.

What's the benefit of not parameterizing your queries? It's so easy to do in most languages, that you just get into the habit and stick to it.

share|improve this answer
A second important problem is plan pollution caused by many unique queries. –  usr Sep 18 '12 at 11:44
add comment

Your Answer


