The Daily WTF: Curious Perversions in Information Technology

2011-06-27 Reply Admin

airdrik:
To all of the people crying over possible SQL injection attacks, while it should be using prepared/parameterized statements, I would venture to say that unless there is evidence otherwise it is probably safe to assume that there is no possibility of such attacks. First, this looks like inner library code. The values passed in were probably pulled directly from the database (myHomeID) or from some hard-coded enumeration (myStatus). Assuming that this is the case, it would be a big WTF if those values did contain some "extra sql" (though that could explain why they were concatenating strings rather than using prepared statements - oh, how the thought makes my brain hurt). Second, if this were somehow exposed to user input, it is probably safe to assume that it is under a layer or two of (most likely WTF-worthy) sanitization/validation code which makes sure that the values conform to some format. That they are using the values as filter conditions in the where clause leads me to believe that neither is linked to a free-form, user-exposed field.

There is no cost to using prepared statements for field substitution. Indeed, its use can improve performance when multiple queries are made. Used consistently, it makes your code provably invulnerable to SQL injection attacks. It avoids other potential bugs that come from data changes (spaces, commas, etc in your fields). For very good reasons, it is the current best practice for forming SQL string queries.

Using string concatenation for SQL field substitution is always the wrong way to do it. It promotes sloppiness in your code base, provides dangerous sample code for people coming on later to the project, and it is a hole in your code where you can't unequivocally claim there is no chance for SQL injection attacks. Arguing that it doesn't matter shows a deliberate disregard for good programming practices.

2011-06-27 Reply Admin

Hortical:
TheCPUWizard:
no u:
. but when you and others say that this is vulnerable to sql injection, this is not based on fact. There is absolutely no evidence of this. The closest you can get is to say that you cannot determine whether it is vulnerable or not - which is hardly a compelling argument.

As others pointed out, the METHOD itself if vulerable. A caller of the method can subvert the functionallity.

This is what defense in depth is all about. You code based on the presumption that the parameters to each method can be "anything" from correct, to silly to downright attacks.

In every method/function on every level? For every type of parameter? Does anyone actually do that?

You do when you are well educated and fresh out of school, or when you work for an organization where efficiency (value for $ spent) or timeliness are of no concern (e.g. the government) or you work on something where it really does matter - e.g. operating systems, medical/military areas etc..

In the most part, no, it makes no sense to spend even 10% of your time covering off all the edge cases for theoretical scenarios which just aren't going to happen.

2011-06-27 Reply Admin

C-Octothorpe:
Lucent:
C-Octothorpe:
You're also failing to think about the junior devs who will maintain this code several years down the road. Do they know about sqli? Probably not. But at least you can be sure you're piece of code is written safely and can handle it.

I'll have to say: websites like this are fairly popular amongst students. If you've seen the problem on here, there's a good chance they've heard of it.

I have one thing to say to this: TDWTF.

Why must the WTF's have been made by juniors? It never mentions their age. Typically they are submitted by someone who inherits a project from a more senior person - someone who was there before and has been in the field longer.

2011-06-27 Reply Admin

Tim:
no u:
I always like to ask candidates who claim to have problem solving skills and/or sql experience the following question:
What is an sql query you could use to return (only) the largest value in a particular column without using the max function. (they have an example table in front of them which I use for other simple questions also). I don't think anyone has ever given me a correct answer.. despite it being ridiculously simple.

I was once asked this in an interview many years ago and the answer seemed trivial.

SELECT -MIN(-theColumn) FROM theTable;

If I had been drinking my iced tea at the moment I read your solution, my screen would now be iced tea stained. Thanks for the laugh. It's even funnier because it works and is a very clever solution to the question. If we were hiring, I would ask you to work for us...lol

2011-06-27 Reply Admin

Lucent:
Why must the WTF's have been made by juniors? It never mentions their age. Typically they are submitted by someone who inherits a project from a more senior person - someone who was there before and has been in the field longer.

Some mistakes are more common from Junior devs. Typically a junior is much less aware of available library functions/3rd party tools as they just have not had the same experience as more senior staff, this leads to them sometimes using incorrect tools for the required job.

2011-06-27 Reply Admin

Andy:
Lone Marauder:
OK, I'll bite:
I'm not a coder, just a lowly network grunt. Can someone explain the WTF here?

SELECT count(*) FROM tblleads WHERE homenum = " + myHomeID + " AND reviewed = " + myStatus;

Would be much faster as the DB can calculate it without fetching the whole (!) table with all (!!) data fields over the DB connection only to ignore the fields and count the rows...

Opening the DB connection only close it right afterwards is not very performant either. But that's another WTF...

In fact "SELECT COUNT(homnum) FROM tblleads WHERE homnum = ? AND reviewed = ?;" is more what he wants.

(Count(*) would be unnecessarily forcing the db to process all fields.)

2011-06-27 Reply Admin

jb:
Lucent:
Why must the WTF's have been made by juniors? It never mentions their age. Typically they are submitted by someone who inherits a project from a more senior person - someone who was there before and has been in the field longer.

Some mistakes are more common from Junior devs. Typically a junior is much less aware of available library functions/3rd party tools as they just have not had the same experience as more senior staff, this leads to them sometimes using incorrect tools for the required job.

Oh, sure, I know. But there is this temptation to blame things on young bucks just because we're older than them. This website was used as proof that they don't know any better. I pointed out that there isn't any proof that the WTF's are caused by juniors unless we assume that they are, using this website as proof to support the assumption that the content of this website is proof...

See the problem?

But, yeah, I broke the build a few times in my day.

C-Octothorpe · 2011-06-27 Reply Admin

Lucent:
C-Octothorpe:
Lucent:
C-Octothorpe:
You're also failing to think about the junior devs who will maintain this code several years down the road. Do they know about sqli? Probably not. But at least you can be sure you're piece of code is written safely and can handle it.

I'll have to say: websites like this are fairly popular amongst students. If you've seen the problem on here, there's a good chance they've heard of it.

I have one thing to say to this: TDWTF.

Why must the WTF's have been made by juniors? It never mentions their age. Typically they are submitted by someone who inherits a project from a more senior person - someone who was there before and has been in the field longer.

Nobody said it was caused by a junion, but I was using them as an example as they are often utilized to perform maintenance on existing applications.

But I do agree, I have seen bad code from juniors, but far worse/scary/dangerous code from old-timers because coupled with ignorance is a shitty "I've been doing this for years" attitude which closes their eyes and ears to any new ideas.

Captain Oblivious · 2011-06-27 Reply Admin

jb:
Hortical:
TheCPUWizard:
no u:
. but when you and others say that this is vulnerable to sql injection, this is not based on fact. There is absolutely no evidence of this. The closest you can get is to say that you cannot determine whether it is vulnerable or not - which is hardly a compelling argument.

As others pointed out, the METHOD itself if vulerable. A caller of the method can subvert the functionallity.

This is what defense in depth is all about. You code based on the presumption that the parameters to each method can be "anything" from correct, to silly to downright attacks.

In every method/function on every level? For every type of parameter? Does anyone actually do that?

You do when you are well educated and fresh out of school, or when you work for an organization where efficiency (value for $ spent) or timeliness are of no concern (e.g. the government) or you work on something where it really does matter - e.g. operating systems, medical/military areas etc..

In the most part, no, it makes no sense to spend even 10% of your time covering off all the edge cases for theoretical scenarios which just aren't going to happen.

There's nothing theoretical about a function getting called outside of the scope for which it was designed. Functions are for code reuse. If your function's implementation is tied to its caller's semantics, you better make sure only the caller can call it. Otherwise, it's fair game.

2011-06-27 Reply Admin

mike:
airdrik:
To all of the people crying over possible SQL injection attacks, while it should be using prepared/parameterized statements, I would venture to say that unless there is evidence otherwise it is probably safe to assume that there is no possibility of such attacks. First, this looks like inner library code. The values passed in were probably pulled directly from the database (myHomeID) or from some hard-coded enumeration (myStatus). Assuming that this is the case, it would be a big WTF if those values did contain some "extra sql" (though that could explain why they were concatenating strings rather than using prepared statements - oh, how the thought makes my brain hurt). Second, if this were somehow exposed to user input, it is probably safe to assume that it is under a layer or two of (most likely WTF-worthy) sanitization/validation code which makes sure that the values conform to some format. That they are using the values as filter conditions in the where clause leads me to believe that neither is linked to a free-form, user-exposed field.

There is no cost to using prepared statements for field substitution. Indeed, its use can improve performance when multiple queries are made. Used consistently, it makes your code provably invulnerable to SQL injection attacks. It avoids other potential bugs that come from data changes (spaces, commas, etc in your fields). For very good reasons, it is the current best practice for forming SQL string queries.

Using string concatenation for SQL field substitution is always the wrong way to do it. It promotes sloppiness in your code base, provides dangerous sample code for people coming on later to the project, and it is a hole in your code where you can't unequivocally claim there is no chance for SQL injection attacks. Arguing that it doesn't matter shows a deliberate disregard for good programming practices.

Yes, I realized that already. In summary: don't be stupid, use prepared statements (and use the functions that are provided which do what you are trying to accomplish).

2011-06-27 Reply Admin

Captain Oblivious:
jb:
Hortical:
TheCPUWizard:
no u:
. but when you and others say that this is vulnerable to sql injection, this is not based on fact. There is absolutely no evidence of this. The closest you can get is to say that you cannot determine whether it is vulnerable or not - which is hardly a compelling argument.

As others pointed out, the METHOD itself if vulerable. A caller of the method can subvert the functionallity.

This is what defense in depth is all about. You code based on the presumption that the parameters to each method can be "anything" from correct, to silly to downright attacks.

In every method/function on every level? For every type of parameter? Does anyone actually do that?

You do when you are well educated and fresh out of school, or when you work for an organization where efficiency (value for $ spent) or timeliness are of no concern (e.g. the government) or you work on something where it really does matter - e.g. operating systems, medical/military areas etc..

In the most part, no, it makes no sense to spend even 10% of your time covering off all the edge cases for theoretical scenarios which just aren't going to happen.

There's nothing theoretical about a function getting called outside of the scope for which it was designed. Functions are for code reuse. If your function's implementation is tied to its caller's semantics, you better make sure only the caller can call it. Otherwise, it's fair game.

I just hope people don't take this into ridiculous territory:

int divide(int a, int b)
{
    return a / b;
}

vs.

int divide(int a, int b)
{
    if (b == 0)
        throw new ArithmeticException();

    return a / b;

And what about checking for overflow when two numbers are added? Do it EVERY TIME? Or only when it seems plausible that overflow could occur?

2011-06-27 Reply Admin

mtj:
In fact "SELECT COUNT(homnum) FROM tblleads WHERE homnum = ? AND reviewed = ?;" is more what he wants.
(Count(*) would be unnecessarily forcing the db to process all fields.)

Not quite.

http://stackoverflow.com/questions/59294/in-sql-whats-the-difference-between-countcolumn-and-count

2011-06-27 Reply Admin

jb:
mtj:
In fact "SELECT COUNT(homnum) FROM tblleads WHERE homnum = ? AND reviewed = ?;" is more what he wants.
(Count(*) would be unnecessarily forcing the db to process all fields.)

Not quite.

http://stackoverflow.com/questions/59294/in-sql-whats-the-difference-between-countcolumn-and-count

tl;dr

2011-06-27 Reply Admin

Captain Oblivious:
There's nothing theoretical about a function getting called outside of the scope for which it was designed. Functions are for code reuse. If your function's implementation is tied to its caller's semantics, you better make sure only the caller can call it. Otherwise, it's fair game.

That depends on your work application environment. I have written many many functions which could be called by other code, but which just plain won't.

I would do standard 'best effort' safety measures to protect other code reusers, but I am not going to add an extra weeks implementation time to a project by coding against every conceivable bad thing which could happen. My employers would rather pay for more functionality and I see a lot of sense in this.

Of course some functions are highly reused and should be written more carefully, and some applications are critical that the whole thing must be written defensively.

Captain Oblivious · 2011-06-27 Reply Admin

Hortical:
Captain Oblivious:
jb:
Hortical:
TheCPUWizard:
no u:
. but when you and others say that this is vulnerable to sql injection, this is not based on fact. There is absolutely no evidence of this. The closest you can get is to say that you cannot determine whether it is vulnerable or not - which is hardly a compelling argument.

As others pointed out, the METHOD itself if vulerable. A caller of the method can subvert the functionallity.

This is what defense in depth is all about. You code based on the presumption that the parameters to each method can be "anything" from correct, to silly to downright attacks.

In every method/function on every level? For every type of parameter? Does anyone actually do that?

You do when you are well educated and fresh out of school, or when you work for an organization where efficiency (value for $ spent) or timeliness are of no concern (e.g. the government) or you work on something where it really does matter - e.g. operating systems, medical/military areas etc..

In the most part, no, it makes no sense to spend even 10% of your time covering off all the edge cases for theoretical scenarios which just aren't going to happen.

There's nothing theoretical about a function getting called outside of the scope for which it was designed. Functions are for code reuse. If your function's implementation is tied to its caller's semantics, you better make sure only the caller can call it. Otherwise, it's fair game.

I just hope people don't take this into ridiculous territory:
int divide(int a, int b)
{
    return a / b;
}
vs.
int divide(int a, int b)
{
    if (b == 0)
        throw new ArithmeticException();

    return a / b;

Is this supposed to be a counter-example? It is obviously better to check "pre-conditions" in the function, or else you have to check for them every time you call the function.

And what about checking for overflow when two numbers are added? Do it EVERY TIME? Or only when it seems plausible that overflow could occur?

Why don't you just stick the check in a function for adding numbers, and then use that? That way, you don't have to think about it at all, and are guaranteed correct behavior.

Captain Oblivious · 2011-06-27 Reply Admin

jb:
Captain Oblivious:
There's nothing theoretical about a function getting called outside of the scope for which it was designed. Functions are for code reuse. If your function's implementation is tied to its caller's semantics, you better make sure only the caller can call it. Otherwise, it's fair game.

That depends on your work application environment. I have written many many functions which could be called by other code, but which just plain won't.

I would do standard 'best effort' safety measures to protect other code reusers, but I am not going to add an extra weeks implementation time to a project by coding against every conceivable bad thing which could happen. My employers would rather pay for more functionality and I see a lot of sense in this.

Of course some functions are highly reused and should be written more carefully, and some applications are critical that the whole thing must be written defensively.

Your functions are doing way too much work if it takes you weeks to code defensively.

Some languages even warn you when your functions aren't total. It takes me about 10 seconds to read the warning and 30 seconds to come up with an acceptable solution.

2011-06-27 Reply Admin

<MasterPlanSoftware> Not a WTF. He was asked to get the count of rows and he did it. The fault is in the spec. You're all stupid anyway. </MasterPlanSoftware>

2011-06-27 Reply Admin

Captain Oblivious:
Hortical:
I just hope people don't take this into ridiculous territory:
int divide(int a, int b)
{
    return a / b;
}
vs.
int divide(int a, int b)
{
    if (b == 0)
        throw new ArithmeticException();

    return a / b;
Is this supposed to be a counter-example? It is obviously better to check "pre-conditions" in the function, or else you have to check for them every time you call the function.

And what about checking for overflow when two numbers are added? Do it EVERY TIME? Or only when it seems plausible that overflow could occur?

Why don't you just stick the check in a function for adding numbers, and then use that? That way, you don't have to think about it at all, and are guaranteed correct behavior.

Like this:

int increment(int x)
{
    if (x == MAX_VALUE)
        throw new ArithmeticException();
    
    return x;
}

And then in any other function we want to increment an int, we call this function?

The additional class/library of math functions you would write to cover all these cases would surely show up here as a WTF.

C-Octothorpe · 2011-06-27 Reply Admin

Hortical:
Captain Oblivious:
jb:
Hortical:
TheCPUWizard:
no u:
. but when you and others say that this is vulnerable to sql injection, this is not based on fact. There is absolutely no evidence of this. The closest you can get is to say that you cannot determine whether it is vulnerable or not - which is hardly a compelling argument.

As others pointed out, the METHOD itself if vulerable. A caller of the method can subvert the functionallity.

This is what defense in depth is all about. You code based on the presumption that the parameters to each method can be "anything" from correct, to silly to downright attacks.

In every method/function on every level? For every type of parameter? Does anyone actually do that?

You do when you are well educated and fresh out of school, or when you work for an organization where efficiency (value for $ spent) or timeliness are of no concern (e.g. the government) or you work on something where it really does matter - e.g. operating systems, medical/military areas etc..

In the most part, no, it makes no sense to spend even 10% of your time covering off all the edge cases for theoretical scenarios which just aren't going to happen.

There's nothing theoretical about a function getting called outside of the scope for which it was designed. Functions are for code reuse. If your function's implementation is tied to its caller's semantics, you better make sure only the caller can call it. Otherwise, it's fair game.

I just hope people don't take this into ridiculous territory:
int divide(int a, int b)
{
    return a / b;
}
vs.
int divide(int a, int b)
{
    if (b == 0)
        throw new ArithmeticException();

    return a / b;
And what about checking for overflow when two numbers are added? Do it EVERY TIME? Or only when it seems plausible that overflow could occur?

Of course you're not going to validate at every layer because that wouldn't make any sense (everything that I've read states that it should be when data crosses trust boundaries, but that definition is flexible).

You'll validate when you (the method or function) are directly acting on the data because you should know how the data should look (bounds checking, divide by zero, etc.). It's funny that, although you would be reinventing the wheel, your example works against your argument because that would be the ideal situation to validate the parameters.

A better example:

public void Deposit(Guid accountId, decimal depositAmount) {
if (depositAmount <= 0)
throw new ArgumentOutOfBoundsException("depositAmount");
// deposit all money into my account! }

Are you really suggesting that I should blindly "trust" the JavaScript validation that may have been implemented in the UI, or that I should perhaps take 2 CPU cycles to have a quick look at them myself?

2011-06-27 Reply Admin

Captain Oblivious:
jb:
Captain Oblivious:
There's nothing theoretical about a function getting called outside of the scope for which it was designed. Functions are for code reuse. If your function's implementation is tied to its caller's semantics, you better make sure only the caller can call it. Otherwise, it's fair game.

That depends on your work application environment. I have written many many functions which could be called by other code, but which just plain won't.

I would do standard 'best effort' safety measures to protect other code reusers, but I am not going to add an extra weeks implementation time to a project by coding against every conceivable bad thing which could happen. My employers would rather pay for more functionality and I see a lot of sense in this.

Of course some functions are highly reused and should be written more carefully, and some applications are critical that the whole thing must be written defensively.

Your functions are doing way too much work if it takes you weeks to code defensively.

Some languages even warn you when your functions aren't total. It takes me about 10 seconds to read the warning and 30 seconds to come up with an acceptable solution.

what are you talking about? I said the project would take an extra week, not that it would take weeks per function, or however else you have misinterpreted it.

It's sad that you spend so much time trying to troll that your largely intelligent answers become lost.

C-Octothorpe · 2011-06-27 Reply Admin

Hortical:
And then in any other function we want to increment an int, we call this function?
The additional class/library of math functions you would write to cover all these cases would surely show up here as a WTF.

You know what, if you can't use your judgement to tell you when something is reasonable, then you're either a troll or a terrible developer.

Either way, I'm done trying to explain colors to someone who is blind...

2011-06-27 Reply Admin

boog:
jb:
mtj:
In fact "SELECT COUNT(homnum) FROM tblleads WHERE homnum = ? AND reviewed = ?;" is more what he wants.
(Count(*) would be unnecessarily forcing the db to process all fields.)

Not quite.

http://stackoverflow.com/questions/59294/in-sql-whats-the-difference-between-countcolumn-and-count
tl;dr

so a comment for someone else was too long for you to read and you thought we'd care to know. quaint.

2011-06-27 Reply Admin

airdrik:
Yes, I realized that already. In summary: don't be stupid, use prepared statements (and use the functions that are provided which do what you are trying to accomplish).

Right on.

2011-06-27 Reply Admin

C-Octothorpe:
Are you really suggesting that I should blindly "trust" the JavaScript validation that *may* have been implemented in the UI, or that I should perhaps take 2 CPU cycles to have a quick look at them myself?

Of course not, I wasn't even talking to you!

2011-06-27 Reply Admin

C-Octothorpe:
Hortical:
And then in any other function we want to increment an int, we call this function?
The additional class/library of math functions you would write to cover all these cases would surely show up here as a WTF.

You know what, if you can't use your judgement to tell you when something is reasonable, then you're either a troll or a terrible developer.

Either way, I'm done trying to explain colors to someone who is blind...

I don't think I have trouble telling if something is reasonable. The people who say ALWAYS and NEVER are essentially overlooking the possibility if being reasonable.

C-Octothorpe · 2011-06-27 Reply Admin

Hortical:
C-Octothorpe:
Hortical:
And then in any other function we want to increment an int, we call this function?
The additional class/library of math functions you would write to cover all these cases would surely show up here as a WTF.

You know what, if you can't use your judgement to tell you when something is reasonable, then you're either a troll or a terrible developer.

Either way, I'm done trying to explain colors to someone who is blind...

I don't think I have trouble telling if something is reasonable.

Well, it's hard to tell because your rampant straw man usage. But hey, if it makes you feel better to 'win' a discussion by bringing it to it's extreme yet logical conclusion, then go ahead.

2011-06-27 Reply Admin

Hortical:
C-Octothorpe:
Are you really suggesting that I should blindly "trust" the JavaScript validation that *may* have been implemented in the UI, or that I should perhaps take 2 CPU cycles to have a quick look at them myself?

Of course not, I wasn't even talking to you!

Who cares?

Take your petty little quabbles elsewhere.

Matt Westwood · 2011-06-27 Reply Admin

So TRWTF is not using a prepared statement to create the SELECT string. Do I win?

2011-06-27 Reply Admin

@Lone Marauder There are two in this example. The first is instead of using the databases count() function he loops through all the results and increments a counter. The second and more major one is the code will allow sql injection by simply adding an apostrophe. The correct way to do this would have been to use a parametrized query.

2011-06-27 Reply Admin

Imagine how much better this code would have been if he had had proper development tools.

2011-06-27 Reply Admin

Daniel Galloway:
@Lone Marauder There are two in this example. The first is instead of using the databases count() function he loops through all the results and increments a counter. The second and more major one is the code will allow sql injection by simply adding an apostrophe. The correct way to do this would have been to use a parametrized query.

Ignoring whether or not it is possible to alter the input, I'd have to question how a single apostrophe would achieve anything more than an sql syntax error.

2011-06-27 Reply Admin

C-Octothorpe:
Well, it's hard to tell because your rampant straw man usage. But hey, if it makes you feel better to 'win' a discussion by bringing it to it's extreme yet logical conclusion, then go ahead.

Funny, I thought that's what you were doing, back there were you were making assumptions as though they were facts. And that part where (someone else, not even you) made an absolute statement and when I questioned its absoluteness, you call me unreasonable?

What gives, man? You like my other sockpuppets.

2011-06-27 Reply Admin

Amtep:
Imagine how much better this code would have been if he had had proper development tools.

That's quite unlikely.

2011-06-27 Reply Admin

mtj:
Andy:
Lone Marauder:
OK, I'll bite:
I'm not a coder, just a lowly network grunt. Can someone explain the WTF here?

SELECT count(*) FROM tblleads WHERE homenum = " + myHomeID + " AND reviewed = " + myStatus;

Would be much faster as the DB can calculate it without fetching the whole (!) table with all (!!) data fields over the DB connection only to ignore the fields and count the rows...

Opening the DB connection only close it right afterwards is not very performant either. But that's another WTF...

In fact "SELECT COUNT(homnum) FROM tblleads WHERE homnum = ? AND reviewed = ?;" is more what he wants.

(Count(*) would be unnecessarily forcing the db to process all fields.)

Ugh, you have no idea what you're talking about.

First, anyone who tells you what the DBMS is going to do based on a snippet of code is an idiot. Second, anyone who calls a DBMS a DB is as clueless as someone who calls WINWORD.EXE a word-processing document.

Count(*) means count all rows, including those that have nulls.

select Count(foo) from X means the same as select count(*) from (select foo from X where foo is not null)

Now for all the people who think they understand SQL: explain why many DBMSs can't just keep a counter on each table so that COUNT(*) is instantaneous.

Kiss me I'm Polish · 2011-06-27 Reply Admin

This should be:

Hey, SQL, can you tell me how many friends I have?
But certainly. You have 3 friends.

But instead it is:

Hey, SQL, can you tell me everything about my friends?
Sure! There's Roy, he's kinda laid back, not very bright but ingenious, about 30 year old, he has some success with women, but his dates end up mostly in disaster. Then there's Moss, he's bright but socially awkward; lives with his mom, but makes up for it with technical skills, around 30, a little scared of women, you'll love him. Finally there's Kate, she's your ambitious graduate who just lands a job with computers by misfortune; she's the youngest, and probably the most adapted of them all; she compensates an obvious technical ineptitude with social skills.
Oh. I have 3 friends, then.

2011-06-27 Reply Admin

Kiss me I'm Polish:
This should be:

Hey, SQL, can you tell me how many friends I have?

But certainly. You have 3 friends.

But instead it is:

Hey, SQL, can you tell me everything about my friends?

Sure! There's Roy, he's kinda laid back, not very bright but ingenious, about 30 year old, he has some success with women, but his dates end up mostly in disaster. Then there's Moss, he's bright but socially awkward; lives with his mom, but makes up for it with technical skills, around 30, a little scared of women, you'll love him. Finally there's Kate, she's your ambitious graduate who just lands a job with computers by misfortune; she's the youngest, and probably the most adapted of them all; she compensates an obvious technical ineptitude with social skills.

Oh. I have 3 friends, then.

Actually, my count is 0.

You do realize that you just described the three main characters from the IT Crowd, don't you?

dgvid · 2011-06-27 Reply Admin

Lucent:
...
Oh, sure, I know. But there is this temptation to blame things on young bucks just because...

Why must the WTF's have been made by young bucks? Sometimes the mistake is made by a young doe. Know why the hunter shot Bambi's mother? She'd saddled him with crap code, that's why!

2011-06-27 Reply Admin

dgvid:
Lucent:
...
Oh, sure, I know. But there is this temptation to blame things on young bucks just because...

Why must the WTF's have been made by young bucks? Sometimes the mistake is made by a young doe. Know why the hunter shot Bambi's mother? She'd saddled him with crap code, that's why!

My, your an unoriginal troll, aren't you?

2011-06-27 Reply Admin

boog:
boog:
boog:
...
Oh, sure, I know. But there is this temptation to blame things on young bucks just because...

Why must the WTF's have been made by young bucks? Sometimes the mistake is made by a young doe. Know why the hunter shot Bambi's mother? She'd saddled him with crap code, that's why!
My, your an unoriginal troll, aren't you?

Says boog of wall people

2011-06-27 Reply Admin

boog:
boog:
boog:
boog:
...
Oh, sure, I know. But there is this temptation to blame things on young bucks just because...

Why must the WTF's have been made by young bucks? Sometimes the mistake is made by a young doe. Know why the hunter shot Bambi's mother? She'd saddled him with crap code, that's why!
My, your an unoriginal troll, aren't you?
Says boog of wall people

Says wall of boog people

2011-06-27 Reply Admin

SELECT TOP 1 field_to_be_counted FROM count_table ORDER BY field_to_be_counted DESC

...?

This of course assumes that field_to_be_counted is numeric, and if not, you'd have to cast as the right data type...

But why are you asking a question like this? How does this actually benefit your company if they can answer this question?

2011-06-27 Reply Admin

The previous comment was of course to the guy saying select max() without using max. Failquote ftw (trwtf?)

2011-06-27 Reply Admin

mysql> select comments from articles where ArticleId = 7128 and CommentType = 'interesting';
Empty set (0.00 sec)

2011-06-27 Reply Admin

Hortical:
Funny, I thought that's what you were doing, back there were you were making assumptions as though they were facts. And that part where (someone else, not even you) made an absolute statement and when I questioned its absoluteness, you call me unreasonable?

It's always wrong to build SQL statements by concatenating strings. Simple as that.

It opens up the possibility of SQL injection
It reduces the chance of the DBMS caching query plans, and so hampers performance.
It gains absolutely nothing, not even readability.

It's just wrong, arguing otherwise is not only pointless, it's encouraging sloppy development.

2011-06-27 Reply Admin

Roman:

mysql> select comments from articles where ArticleId = 7128 and CommentType = 'interesting';
Empty set (0.00 sec)

TRWTF is YourSQL.

2011-06-27 Reply Admin

SELECT COUNT(*) FROM InterestingComments

(0 row(s) affected)

2011-06-27 Reply Admin

To everyone that is discussing wether this code is vulnerable to SQL injection:

Note that the method is [b]PRIVATE[b]! Hence, we know that any of the calls to this method can only be done from other methods in the same class.

Of course, it would be infinitly better to use parameterized queries and NOT concatenate a SQL query, and judging by the code there is a significant risk there are no encoding what so ever, or that the encoding is buggy or broken, but we don't KNOW that.

Someone mentioned reflection can call this method with unsafe parameters. That's true, but if an attacker can run such arbitrary code with sufficient permissions, the system is compromized anyway and there is nothing that prevents an from accessing the DB directly.

(If a developer decides to use reflection to call this method, then THAT is the WTF, not the accidental bypassing of parameter encoding.)

2011-06-27 Reply Admin

Steve The Cynic:
Andy:
Opening the DB connection only close it right afterwards is not very performant either. But that's another WTF...
At least he closes it. We've seen may too many examples that just leak connections like the proverbial sieve.
But I'm with you on the count thing. I'm no squill expert, but at the only place I've worked that did much of it (where we called it squill, ok, so don't get at me...) we used count() a lot. I always had a vague disquiet, because it felt like count(*) should return N times the number of rows, where N is the number of columns. Fortunately, the people who built SQL had more sense than me in that respect...

With the chance of a fight in mind, [s]I disagree entirely[/s] you're not very bright, are you?. Count(parameter) counts the number of things like the parameter passed it.

= an entire row count(*) = count how many entire rows we have

Seems intuitive to me

2011-06-27 Reply Admin

no u:
Canthros:
I'm mostly reminded of a job interview where the technical portion of the interview asked a SQL question about getting the largest value from a given column. Max() being the answer, of course. I asked if it was a trick question or if they were after something obscure involving OLAP or something, it seemed too obvious when the job description specified SQL experience.
Now, I see that my faith in humanity is just too high.

I always like to ask candidates who claim to have problem solving skills and/or sql experience the following question:

What is an sql query you could use to return (only) the largest value in a particular column without using the max function. (they have an example table in front of them which I use for other simple questions also). I don't think anyone has ever given me a correct answer.. despite it being ridiculously simple.

I was once asked this in an interview many years ago and the answer seemed trivial.

I'll bite, despite not being experienced in the ways of DBS

select * from 
(
select <column_name> from <table_name> 
order by <column_name> desc
)
where ROWNUM=1;

2011-06-27 Reply Admin

With regards to SQL, it is completely acceptable assuming that the input is correctly escaped to make SQL injection impossible. These "NEVER" comments tend to identify coders that have only been exposed to very limited problems and environments.

With regards to non-SQL string concatenation NEVER being acceptable, you are an idiot.

A More Better Way to Count

Leave a comment on “A More Better Way to Count”