not using prepared statements is the first bad practice. Not escaping data before inserting it into a query string is the second.

There are no security vulnerabilities associated with this particular example. Problem is that most application consist of more than just a single query with date() as the only input. There may be 100 or even 1,000 queries. If you adopt unsecure practices, there's a huge risk of getting one of those many queries wrong -- and this might already be enough to break into your application.

Security is about thinking ahead and avoiding risks. It's not about gambling and finding the lowest possible protection ("How many strings can I leave unescaped before I run into an SQLi vulnerability?").

That's what you need to understand.

You can post hundreds of other examples, and we can discuss all day long about whether they're dangerous or probably dangerous or not dangerous at all. But that's not the point. The point is to not let any string cause any harm, regardless of who created it and where it comes from.

Why canít I use certain words like "drop" as part of my Security Question answers?
There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

Not to beat a dead horse but if that date is the only thing in the query that varies then it's pointless to use a prepared statement. Goes back to the "I'm not trying to escape values in the query myself but rather I know that the input cannot possibly contain anything that may need escaping" issue I keep making.

Hey, I just found a way to optimize our communication! All threads of this kind basically have the same structure:

zxcvbnm has found an example of some unescaped value, and he asks whether this is dangerous or not.

I tell him stop the stupid vulnerability gambling and always use prepared statements to pass input to a query.

You come around and tell him that this particular value is safe and that he doesn't need a prepared statement in this particular case, giving him the chance to ask the same question for a different example next week.

Wait one week, go to 1.

This is obviously inefficient and involves a lot of redundant communication. So how about an optimized approach?

Whenever zxcvbnm has to write a query with input, he sends you a list of all input values.

You go through the values and rate them: "safe", "probably safe", "dangerous"

zxcvbnm leaves the "safe" values unescaped and escapes the others.

If you were wrong, you take care of any possible damage.

Sounds great, doesn't it? I wrote a small Perl script for this which I can send you.

Wow, this is sooo much smarter than simply telling zxcvbnm to use a generalized approach. And it's also much more fun. Simply putting an end to SQL injections is kinda boring. Rating each input individually, on the other hand, is a real challenge, and it keeps the adrenaline pumping. "Will I get away this time? Or will I have to repair his server again?"

Fun, fun, fun!

For even more fun, you can also consider leaving the "probably safe" values unescaped.

Comments on this post

Why canít I use certain words like "drop" as part of my Security Question answers?
There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

See, I have this controversial opinion that people should understand why things are safe or unsafe, and that I shouldn't just dismiss the entire question with "don't think about it just use prepared statements all the time".
But you know what? If you don't think people are capable of learning things like that then go right ahead and continue saying what you're saying. Meanwhile I'm going to hang around and try to explain what's going on.

Which reminds me, I never did explain it.

date("Y-m-d") will only result in numbers and hyphens. Like with md5() and sha1() and most hashing functions in PHP that return hexadecimal characters, there's absolutely no risk of SQL injection (provided you put the value in quotes like you're supposed to do with all inline strings).

Maybe we have a different understanding of "explaining" things. You answered the question by pointing out that this particular value cannot contain any dangerous characters. That's a true statement: If you use this exact query in your code, you don't add an SQLi vulnerability.

But does that solve the problem? No. It doesn't solve anything.

If you feel experienced and lucky enough to assess the danger of every single input value, that's fine. Go head. But zxcvbnm obviously cannot or does not want to do that, which is why he's asking us again and again. And I totally understand this, because I don't wanna take this risk either. Security vulnerabilities can be very subtle, and I don't consider myself to be perfect and detect every risk at any time.

So what now? We can keep playing this stupid "ask the expert" game forever: zxcvbnm posts a query, and we (the alleged "experts") assess the risk. Or we can explain how to solve the underlying problem: Make sure that data is always treated as data and not as code. Then you don't have to analyze every single input value.

And just to make sure: The date() function itself can cause SQLi vulnerabilities, because its output is not limited to certain characters. The only reason this one is not dangerous is because it's hard coded and doesn't contain any of the critical characters (quotes, slashes, etc.). Pretty thin ice, if you ask me.

Why canít I use certain words like "drop" as part of my Security Question answers?
There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

Thank you for replies. Both requinix and Jaques1 are knowledgable and an asset to this forum,but there are a few things I would like to respectfully mention:

Just because you know and understand something - and it makes perfect sense to you -, it doesn't mean it has to be the same for everyone else as well. Other may have higher or lower level of understanding and applying knowledge.

In an argument, both parties loose! There is no point in trying to convince others. Your time is valuable, remember you are an asset

The question asked above is valid in my opinion. It is not redundant post. I can simple say "Ok PDO bind, DONE" and copy past code without realising why. But if you read my original post carefully, you will see that I was not even speaking about security. You are a little too much focused on security. Someting like this came up:

PHP Code:

$sql = "...AND allotments_new.day_date >= 'Warning: date(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'Australia/ACT' for 'EST/10.0/no DST' instead in /home/h2g/public_html/footer_nick.php on line 262013-07-18'
AND allotments_new.day_date <..."

Please keep being nice and friendly and continue sharing knowledge patiently. I am sure it is not personal. I strongly recommend this for friends. Excellent read.

When you have a specific issue in mind, it might be a good idea to actually tell us that. We suck at reading minds, so relying on us to somehow figure out your "hidden question" is likely to fail.

Either way, the security discussion still holds true. If you commited something like this at our company, you'd have a very long and unpleasent talk with our tech guru about not jeopardizing our clients just to save a few characters.

Why canít I use certain words like "drop" as part of my Security Question answers?
There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".