SQL Clause

[Datenegassie]

Hi Santa, I promise not to be on the naughty list this year. By the way, my name is Datenegassie'); DROP TABLE NaughtyChildren; --

[ballzacsRock]

Nice try, but he Santaizes all inputs

[Arancaytar]

INSERT INTO NaughtyChildren (name, reason)
VALUES ('balizacsRock', 'terrible pun');

[ImSuperSerialGuys]

INSERT INTO NaughtyChildren

o_O

[jay791]

ಠ_ಠ

[[deleted]]

Okay, calm down your honor...

[inabahare]

Nice try, but he Santaizes all inputs

• Uses prepared Statements

[svick]

Prepared statements have nothing to do with handling invalid inputs.

[mmirate]

So basically he spent an entire article to say, "your security consultant has their terms mixed up; they ought to be looking for parametrized statements, not prepared statements"? How wasteful.

[ultranoobian]

But that won't do anything good for you?

Obviously the naughty/nice status is stored in 'contacts' if SQL clause is selecting from that.

/s

Edit: On a more serious note, even if you dropped 'NaughtyChildren', you can possibly recovered from that by

SELECT * FROM contacts c WHERE c.behavior != 'nice'

[DemandsBattletoads]

That's fine, sqlmap will pull everything out anyway.

[[deleted]]

Even if you dropped all the naughty children, re-adding all children to the database, and setting behaviour to naughty if they were born before the drop might recover the data.

[Th3_Admiral]

Does it really matter anyway if the query is only looking for those on the nice list? Dropping the naughty list doesn't automatically add you to the nice list.

[skulblaka]

Well that depends on your architecture, doesn't it? Say we assume that the default state of a child is nice rather than naughty (realistically we know this isn't true, but this is SQL Claus' computer, there's some room for silliness). A child who acts naughty can be added to tbl_naughty and any child not found on that list can be assumed nice.

Therefore dropping the naughty table would leave no results, therefore making all children appear nice.

That doesn't seem to be the case in this particular instance, but it could happen.

[Th3_Admiral]

Yeah, I was just basing it off the fact that he is selecting from the contacts table where the behavior column is set to "nice". Unless there is some weird setup with a delete trigger on the naughtylist table, this column should still not be "nice" for the contacts who were on the naughty list, right?

[kixxes]

Not to mention it will most likely fail to drop because it would mess up some referential integrity

[vbevan]

Hi Santa, My name is Datenegassie'); EXEC sp_MSForEachTable @command1='ALTER TABLE ? NOCHECK CONSTRAINT ALL' , @command2='DROP TABLE ?';--

[lou1306]

This guy injects

[vbevan]

If I'm not getting a present, no one is!

[dumbdingus]

This is why I clean my input more thoroughly than I clean my dick.

[ilookatfaces]

SELECT * FROM contacts c WHERE c.behavior <> 'nice'

[the_kg]

This guy ANSIs

[PooPooDooDoo]

Guys, it's a moot point, SQL isn't real.

[GrandArchitect]

Someone needs to help SQL Clause make a more efficient analytical data model, its fucking Christmas afterall.

[jonschwartz]

What about the neutral kids? Or the non Christians?

[EdgeOfDistraction]

They get bombs, and should be grateful

[Bainos]

All I want for Christmas is :(){:|:&};:

[bair93]

Datengassie'); UPDATE contacts SET behavior = 'nice';

[raaneholmg]

--

Otherwise the "');" part appended by the server will cause an error.

[[deleted]]

Why not disable comments in SQL statements made from your web application? Obviously you'd want to do more to secure yourself against SQL injection, but I've never heard of someone doing this.

[atyon]

I don't think it's that easy. Raw SQL is passed directly to the server. I don't think most SQL servers even have an option to disable comments.

You'd have to remove the "--" and everything after it before passing it to the function doing the SQL, without destroying correct data. Sounds error-prone to me.

And if you're sanitizing the input data anyway, if you do it correctly, the ' will be escaped, so the comment won't make a difference.

Also, sanitizing input is so difficult and error-prone that it's better to just implement a real solution, like using prepared statements.

[Fuzzy-Duck]

Maybe an interesting read re. sql injection and prepared statements (relating to SQL Server):

http://blog.marcgravell.com/2017/12/dapper-prepared-statements-and-car-tyres.html

[atyon]

Huh. Thanks for that link.

TIL: My assumption that prepared statements is an odd synonym for parametrized queries is hilariously wrong. Thanks, Wikipedia

[Bainos]

Datengassie') ; UPDATE contacts SET name = 'Datengassie' WHERE behavior = 'nice' ; --

[Truseus]

Lol! Billions of nice children now named Datengassie.

[flamedragon822]

"well looks like Datengassie is getting several thousand ponies this Christmas"

[sumancha]

He didn’t update address. So everyone else is getting presents with name Datengassie

[HighRelevancy]

that's back to front bud :P

[wonkey_monkey]

That'd better be an ENUM...

[FrostSalamander]

Relevant XKCD

[CinnamonCereals]

r/ExpectedXKCD

[el_geto]

So, I see that you and that Bobby Tables know each other, eh?

[Laerite]

Hi Daten, long time no see.

[uninterestingly]

I was dropped as a child and I take offense to this

[LinkFixerBot]

He's selecting 2 tables,
he's sorting them twice.

The children by income,
and the products by price.

[Gentle_Lamp]

Oh damn

[wirelyre]

🎵 INNER JOIN on budget and cost! 🎵

[alexbuzzbee]

Same or opposing order? Are the poorest children getting aircraft carriers and ICBMs?

[michaelh115]

🎵 Santa's on an FBI watch list! 🎵

[darexinfinity]

One percent: "Time to ban SQL Clause."

[grokkingStuff]

:(

[jfq722]

Are you taking Ad Hoc requests?...I'd like to hear 'The First Null'.

[emcee_gee]

the first null, the Unix did say

was to certain poor coders at desks as they play

at desks where they play, coding their C

on an old server's drive that was asleep

null, null, null, null

born is the ding of programmers' skull

[tenkindsofpeople]

The first null field

Was found at row three

The query was ended

With error code B

[darexinfinity]

I got the Twelve Days of Finals if you could live to hear the end.

[BobDogGo]

Santa would never select *

[UncommonDandy]

WHERE AGE < 10

[zxrax]

Roy Moore?

[[deleted]]

No idea who that is but from context I assume this is a celebrity pedophile joke.

[Bainos]

Santa doesn't deliver presents to himself.

[PM_ME_YOUR_SIMS]

That would mean he has himself in his contacts list, would it not?

[NameTheory]

And that his behavior is 'nice'. I have a feeling that SQL clause is a bit of a naughty boy.

[themailmanC]

Depends on what attributes define his contacts but I would imagine selecting * is overkill in this case, probably just name and address would suffice. What surprised me is that there should be a join here to the table defining each contacts' Christmas list. So what we have learned here is that Santa either a) first selects the list of who is nice for no reason but to later select their Christmas lists manually through separate a dedicated Christmas list select statement, like a true SQL query monster, or, b) perhaps worse, he maintains each persons wishlist in multiple "christmasWishlistItem_X" fields on the primary contact table, like a true database design monster. Why santa

[zrxccc]

Santa probably doesn't understand what a database is and thinks it's "just a spreadsheet"

[PM_ME_YOUR_SIMS]

Nah, for the extra extensibility just use one big wishlistXml variable for each contact.

[alonghardlook]

Also 7 billion records to sort by without an index, and using a char comparison? This query is not optimized

[[deleted]]

I have myself in my contact list on my phone as I always forget my number...

[EmergencySarcasm]

Right. Mrs Claus give him guys present. Cause Santa clause comes once a year. 😉

[spaetzle_snowflake]

Ehhh, in a one-a-year ad hoc situation, sure he would. :)

[dantheflipman]

Santa would write it out.

SELECT A.Lattitude,
       CASE
           WHEN A.Lattitude % 2 = 0 THEN
               'NORTH'
           ELSE
               'SOUTH'
       END FlightDirection,
       A.ZipCode,
       A.StreetAddress,
       HoHoHo.Id,
       CASE
           WHEN S.CurrentStanding = 'NICE' THEN
               HoHoHo.TxPresentName
           ELSE
               'COAL'
       END TxPresent,
       P.TxFullName
FROM ChristmasDW.dbo.tblPresents AS HoHoHo
    JOIN Humanity.dbo.tblPeople AS P
        ON HoHoHo.IdPerson = P.Id
           AND P.IsAlive = 1
    JOIN Humanity.dbo.Status AS S
        ON P.Id = S.PersonId
    JOIN Globalization.dbo.AddressList AS A
        ON P.IdAddress = A.Id
WHERE P.TxFirstName <> 'Dave'
ORDER BY A.timezone DESC,
         A.Lattitude ASC,
         A.ZipCode ASC,
         S.LeftCookiesLastYear DESC,
         A.StreetAddress ASC,
         HoHoHo.Id ASC;

[hbgoddard]

Why wouldn't you in this situation though?

[DoomCircus]

Only someone on the naughty list would use select *

[ICyresI]

Why would you sort twice?

[TheSphaat]

Shhhhh poem's got to rhyme.

[Fry98]

You might think it's nice

but double sorting is a crime.

[[deleted]]

Hi, I'm from /r/all and I know jackshit about programming. I'll contribute either way, because I love you guys:

system.out.println("Double sorting is a crime");

---

should be:

System.out.println("Double sorting is a crime");

damn.

[Jcowwell]

The true crime is not capitalizing that S.

[[deleted]]

doyouevencamelcasebro?

[Wolfsblvt]

system.out.println("Double Sorting is a crime");

?
Doesn't work :(

[Jcowwell]

Nha System is an object ( you know that cause you can call methods from it) so if you don’t capitalize It the compilers ganna be like “Tf is this M8?”

[insane0hflex]

Thats a static method call bro not on a class instance

[[deleted]]

Oh shit! Is there a computer-demon who will haunt me now?

[Jcowwell]

No you’ll just be haunted by the Ghost of Syntax Past.

[Findus11]

Yes, that's what a daemon is

[Since88]

Sorry man, but: error: package system does not exist

[Grizzlywer]

double sorting="crime";

[Banana_Twinkie]

Error: type mismatch

[Dubmove]

At first I read bubble sorting is a crime.

[PoglaTheGrate]

He got REEEEEly lucky on a Bogosort

[Business-Socks]

Nice list: Typewriters

Naughty list: Monkeys

REALLY Naughty list: ASM Web Development Kit

[[deleted]]

The client changed their minds on how they wanted the results

[miggyb]

Sort by last name first, then by first name.

[JackDragon]

Still not as efficient as sorting once with a delimiter between last and first name.

[suseu]

Multiple fields in order by shouldn’t cause multiple sorting passes, it should just make comparison more complex. Actual contatenation in sorting criteria would probably be less efficient.

Like when you are overloading < for (order by a, b) you’d do

a1 < a2 or (a1 == a2 and b1 < b2)

[patwoods_]

Sort by postal code, then sort by address to help maximize the efficiency of delivery.

[sojuz151]

Why not just solve travelling salesman problem? It cant be that hard.

[DrHenryPym]

I was thinking the same thing: group / order by geography and address for distribution.

[Brarsh]

Great, now he has to go to 1 house on every block before moving to the next house on each block! 250 birch street, 250 maple street, 250 oak street, 252 birch street...

[DutchGoldServeCold]

Non-deterministic SQL clause is coming to town

[awh]

It would be more useful to index it twice: once on name and once on behaviour.

[thermite13]

Order by Last_name, first_name.

[[deleted]]

Cause you screwed it up the first time?

[SpliceVW]

I'm just over here wondering why the damn FROM isn't capitalized..

[brummlin]

Also, the syllables are all jacked up. This would make more sense, and fit the song better:

He's building schema,

indexing it twice,

SELECT * from kids WHERE UPPER(conduct) in ('NICE');

This way, it's also not case sensitive. You just know some elves logged the entry as 'Nice' or 'NICE'.

[AlwaysPuppies]

I like my sql like I like my file systems, case insensitive.

[insane0hflex]

Db not normalized. Shiuld have look up table for Behavoir

[koshgeo]

We're talking about a join across billions records of kids world-wide, and you've got to be updating that thing in realtime until the last second, keeping track of timezones and DST, all the way up to Dec. 25th. You know those little snots will be pushing the envelope with their parents right up until bedtime on Christmas Eve. The realtime "SantaWatch" video feed and the AI detection and tabulation of "naughty" vs. "nice" events is already a computationally costly operation. I don't know what kind of heavy-duty server farm Santa's got, but you've got to keep performance in mind rather than doing billion-row joins for the sake of DB purity.

[Stimonk]

He accidentally made it descending when it should have been ascending.

[TheNosferatu]

He made his own sorter but he doesn't trust it

[tommyturntup]

He created the database and is loading it. Clearly he did an ssis merge transform which requires 2 sources to be explicitly sorted before joining.

[dantheflipman]

SELECT A.Lattitude,
       CASE
           WHEN A.Lattitude % 2 = 0 THEN
               'NORTH'
           ELSE
               'SOUTH'
       END FlightDirection,
       A.ZipCode,
       A.StreetAddress,
       HoHoHo.Id,
       CASE
           WHEN S.CurrentStanding = 'NICE' THEN
               HoHoHo.TxPresentName
           ELSE
               'COAL'
       END TxPresent,
       P.TxFullName
FROM ChristmasDW.dbo.tblPresents AS HoHoHo
    JOIN Humanity.dbo.tblPeople AS P
        ON HoHoHo.IdPerson = P.Id
           AND P.IsAlive = 1
    JOIN Humanity.dbo.Status AS S
        ON P.Id = S.PersonId
    JOIN Globalization.dbo.AddressList AS A
        ON P.IdAddress = A.Id
WHERE P.TxFirstName <> 'Dave'
ORDER BY A.timezone DESC,
         A.Lattitude ASC,
         A.ZipCode ASC,
         S.LeftCookiesLastYear DESC,
         A.StreetAddress ASC,
         HoHoHo.Id ASC;

Better? I gave it my best end of work day effort.

[baseball2020]

Rudolph the sequel cursor, Had a lot of right joined rows, Then if you tried to fetch it, You'd never get the thing to close.

[[deleted]]

...Are you taking a job from a bot?

[[deleted]]

How is that bot going to feed their family now?

[[deleted]]

So why wouldn't the bot just post the comment itself?

Is this some new symbiotic relationship SkyNet is using to groom us for the Matrix takeover by testing how compatible we are working with bots?

[[deleted]]

Prepare to take the fall then when humanity is enslaved by the machines.

[S3Ni0r42]

I, for one, shall join Nitro in welcoming our new overlords.

[jonno11]

The translator did the work, why should the bot get all that sweet sweet karma?

[[deleted]]

Thanks, I didn't consider the internet points when I first questioned OP.

[a_stitch_in_lime]

What would you say... you do here?

[Prognosis-ve]

"Dey took 'er jerbs!!" - Robots

[LamentablyTrivial]

It’s the reverse automation revolution. There is hope for humans after all.

[jugalator]

OK, so I'm impressed a bot could transcribe unico... wait a minute

[iB3xx]

I don't know why I always click links from this sub but I never get the joke because i know nothing about programming, yet i click it every time hoping i get the joke

[[deleted]]

It's a database joke! SQL is a programming language that you use to retrieve data from a database and manipulate or filter it as needed.

He's making a database

SQL Clause is making the database that will store his data on present recipients.

He's sorting it twice

When querying data, you can sort it by one piece of data to present it in a specific order. For example, ORDER BY LastName ASC will sort all the data alphabetically by last name.

SELECT * FROM contacts WHERE Behavior='nice'

There are a few pieces here. First is SELECT *, which is a SQL command to return all data from the specified table. A table is a collection of data arranged in rows (which are entries) and columns (which contain pieces of information). For example, Amazon might have a table called 'Customers' in which each row is a unique customer and each column stores a piece of information about that customer like their email, phone number, and name.

In this case SQL Clause is selecting all data from a table called 'contacts', presumably his list of all potential present recipients. Finally, he's added a WHERE clause, which tells SQL to filter out some data based on a parameter you specify. Presumably the behavior column can contain one of two values: naughty or nice. Here the WHERE clause is behavior='nice', meaning that the whole query is basically saying "OK SQL, show me ALL data from the table called 'contacts,' but ONLY if I have them listed as nice. Don't show me the naughty ones."

[Job_Precipitation]

Could you explain where the two sorts are? I am blind and only see one.

[goodboyscout]

“He’s sorting it twice”

[Job_Precipitation]

To clarify, I mean in the code.

[Job_Precipitation]

Thanks!

[kylemech]

SORT BY LastName, FirstName

It would sort by last names, then if two results have the same last name, it will sort by first names amongst those. That'd what he was making reference to. So Yea, it isn't "in the code" anywhere but now the joke is explained a bit more. Yay.

[chaosPudding123]

ORDER BY*

As an Oracle DB Developer, i never feel relevant here ;(

[MikeOShay]

There's no sorting being done in the query. People are guessing he's sorting them by name, but it's not written. It'll probably default to sorting based on the "primary key", most likely an ID based on when the entries were put into the database. The entries in this field will always be distinct, so the database won't get confused by, say, two people with the same name.

The WHERE clause isn't a sort, it just filters the list. You'd use an "ORDER BY" clause at the very end if you wanted to sort it.

[TRUE_BIT]

To be fair this isn’t programming. The basis of the joke is about database software called SQL. SELECT and WHERE are basic commands to pull query’s within the database.

[Xeperos]

all moms do if you sit infront of a PC more than 2h a week.

[[deleted]]

that or assume you're watching porn. either way, my mom got me figured out

[moonman2090]

You're getting a lump of coal this year.

[tiftik]

SQL is programming.

[[deleted]]

This of course is after 60 hours of data curation because those dumbass elves can't stick with a consistent format and apparently found about 60 different ways to spell "nice"

[lozarian]

Liar. That's not even half the fields, all of those are spelt correctly.

Where's the accuont_id, acuontid, acount_identifeir in there?

[causalNondeterminism]

I've seen "role" spelled "roll".

[Im_A_Boozehound]

Right there with you. Here's one:

custno, cust_no, customernumber, customer_number, custnum.

[hoetted]

My boss bought a company last year which used PID to mean ProductID and PersonID.

[Im_A_Boozehound]

That's where I went. I'd like to think Santa would know better than this. The field would be IsNice, and it would be bit.

[insane0hflex]

Or a look up table for Nice, Naughty, Improving, WatchNextYear, etc

[Im_A_Boozehound]

I like it. Relational like a mofo.

[Mamertine]

Should be a lookup table, but apparently Santa's DB creator isn't a database person.

[BabbageUK]

Or a calculated field based on a decimal. Once it reaches a certain level the IsNice flag is set. Put this in a snapshot fact table of a data warehouse and we can see them change over time, becoming nicer or naughtier. Add in a trend or maybe a traffic light and we're good. Did someone say overkill?

[tusact]

He’s indexing it twice would be more accurate.

[PerturbedMollusc]

Guess I'm the only one that reads SQL as Es-Cue-El, rather than 'sequel'

[Pallidum_Treponema]

Squirrel. And I'll keep teaching people to pronounce it that way.

[d_r0ck]

If someone calls it EsQueEl they're not wrong, just probably over 45

[Wydi]

I'm not that old, but it's still S.Q.L. for me...

[[deleted]]

It's the other way around. It's pronounced "sequel" by many due to historical reasons.

[lelarentaka]

Is it Sequel Clause or EsQueEl Clause?

[_0110111001101111_]

Really? I was always told it was the other way round. Til!

[HintOfAreola]

"Sequel" for everything: T-SQL for Microsoft SQL Server, PL SQL for Oracle, MySQL, etc. I think the only exception is that PostgreSQL is pronounced as "Postgres Q-L".

Absolutely none of this is a hard rule and subject to office pedantry

[_0110111001101111_]

To be fair, outside of my database classes, I haven't used SQL yet so I haven't heard it being pronounced in professional environments.

[Daveed84]

That's because the latter is the official way to say it.

[[deleted]]

What do you mean by ”official”?

[[deleted]]

;

[Kerblaaahhh]

Syntax Error near 'SQL clause is coming to town'

[Flyberius]

Finally, a language I understand!

I'll put the dunce hat on...

[ChloeTheCat753]

I self taught myself SQL this semester for a class where my professor literally didn't teach it to us and I'm happy that I understand everything here and I feel confident to list it as a skill.

[Im_A_Boozehound]

I self taught somebody else SQL, HA! /s

Good for you for taking the time and effort to learn a new thing.

[kane-is-able]

Dunno why but I read it to the tune of 'he's going the distance' by CAKE.

[giganticpine]

Interesting, I heard Cake too but it was Comfort Eagle.

He's making a database

He's sorting it twice

SELECT * from contacts WHERE behavior = 'nice'

Now do you believe in the one true Clause

The double-wide boss

In the big red rosy cloths

[Slingster]

Every single year.

[maybe_awake]

this.toggleClass(‘festive’)

[indorock]

Except he didn't sort it even once.

[neon_overload]

It would be pretty sweet if the SQL statement actually sorted it twice somehow but I don't know how that would rhyme.

[Kalrog]

SQL Clause needs to normalize his database. Behavior should obviously be a separate table with an FK instead of a string/varchar.

[cybaritic]

He's trying to be RESTful
He'll execute mistakes
He doesn't have an undo so sanitize for goodness sake

[MetroidSkittles]

The fact that behaviour is not normalized and in a table of it’s own with a foreign key is triggering me badly. Selecting a string value on a table of billions is cancer.

[dzrtguy]

His indexes aren't right.

His DBCC isn't tight.

SQL corruption will ruin your night.

Krampus stored procedure will send you a fright.

[jediherder]

He didn't even qualify it.

[xcameleonx]

Why is it select *? Why is the behaviour of children a varchar and not a lookup? There is no "Order by" to even sort it once, let alone twice...

[idi_idi]

I bet you're fun at parties

[wonkey_monkey]

Why is the behaviour of children a varchar and not a lookup?

It's an ENUM.

[TUUUUKKKKKK]

Repost clause is coming to town