Comment On Solution Injecting

XML blows.

Alex Papadimoulis:

The Entity Manager will present all business entities in a common format, the Big-5 consultant raved, allowing any application within the enterprise to access the CRM system.
That, and the bargain-basement rate of £1000/developer/day, made the
Entity Manager-based CRM system an install sell to management. A year
and a few million pounds later, Mike Barker's company was the proud owner of a somewhat-functioning, fairly buggy CRM system.

But none of this really impacted Mike; his group had nothing to
do with the CRM system. That is, until his system was required to
interface with the CRM. Fortunately, working with the Entity Manager
wasn't too bad. It was simply XML-in, XML-out ...

In order to retrieve an EntitySet, you will need to create an
EntityQuery and pass it to the EntityManager. An EntityQuery can
request any number of different Entities to retrieve and will return
zero or more of each type requested based on the parameters. The
EntityQuery must be in the following format:

<EntityQuery>

  <Entity Name="{ENTITY_NAME}">

    <Fields>

      <Field Name="{FIELD_NAME}" Value="{FIELD_VALUE}" />

      <!-- ... More Fields ... -->

    Fields>

  Entity>

  <!-- ... More Entities ... -->

EntityQuery>

But as he read further in the Entity Manager documentation, Mike learned the CRM was a bit more WTF than he thought ...

To query a date field over a range, the value of the query field must be exactly
"{START_DATE}' OR {FIELD_NAME} BETWEEN '{START_DATE}' AND '{END_DATE}".
For example, to retreive all ClientContacts from the month of April,
2003, simply use the following query:

<EntityQuery>

  <Entity Name="ClientContact">

    <Fields>

      <Field 
        Name="ContactDate" 
        Value="2003-04-01' OR ContactDate BETWEEN '2003-04-01' AND '2003-04-30"

      />

    Fields>

  Entity>

EntityQuery>

Any chance this was designed by the same creators like the WMF file format?

public class DropTableCommand : EntityQuery

{

    //TODO: implement

}

{COMMENT_TEXT}

...To query a date field over a range, the value of the query field must be exactly "{START_DATE}' OR {FIELD_NAME} BETWEEN '{START_DATE}' AND '{END_DATE}"....



what's the reason for this madness?

xml!

run!

ahh.... xml-abuse!



Enjoy. You'll be seeing crap like this until you retire.

DiamondDave:

...To query a date field over a range, the value of the query field must be exactly "{START_DATE}' OR {FIELD_NAME} BETWEEN '{START_DATE}' AND '{END_DATE}"....

what's the reason for this madness?

Cause they are taking the input and building a SQL statement, so the first 'date' welds well with 'WHERE DATE =' + passed in value.  and then the 'OR Fieldname BETWEEN Then and Now'  does the real work.  Basically they are teaching their users to do SQL injection attacks against their own system.

Good WTF, nice and subtle as a Tuba.

Awesome! An XML SQL client. Why didn't anyone think of this before?

Whenever anybody says how great XML is, I just remind myself that XML is just a text file format. So if you take out "XML" and replace it with "text files" it sounds much less exciting and revolutionary.

DiamondDave:

...To query a date field over a range, the value of the query field must be exactly "{START_DATE}' OR {FIELD_NAME} BETWEEN '{START_DATE}' AND '{END_DATE}"....



what's the reason for this madness?

My guess is that it's distressingly simple. The badass query engine is
nothing more than a SQL concatenator. It's designed to append elements
to the WHERE clause by field. The sub-WTFery comes about because the
concatenator was designed to concatenate " where " + fieldNameParam + "
= " + valueParam; that is, searches by value, not range.



The {START_DATE} at the beginning is designed to ensure there's no SQL
error, otherwise you'd have something like " WHERE create_date =
BETWEEN '1/4/2005' AND '1/7/2005' ". This is what tips the gaff and
indicates that the Emperor is nekkid.

Me slow.

DiamondDave:

...To query a date field over a range, the value of the query field must be exactly "{START_DATE}' OR {FIELD_NAME} BETWEEN '{START_DATE}' AND '{END_DATE}"....



what's the reason for this madness?

Because they didn't really implemented a range search; it's done by SQL injection.



Put simply they have code like that:

String sqlstmt = "select * from "+entityName+" where "+fieldname+"='"+value+"'";

resultset = stmt.execute(sqlstmt);

Normally, you give it "customer", "id" and "123", and it does

select * from customer where id='123'

With that special style, you give it "customer", "id" and "123' or id between '123' and '456"; it results in

select * from customer where id='123' or id between '123' and '456'

This style of programming is not only ugly, it allows for various attacks by evil users.

DiamondDave:

...To query a date field over a range, the value of the query field must be exactly "{START_DATE}' OR {FIELD_NAME} BETWEEN '{START_DATE}' AND '{END_DATE}"....

what's the reason for this madness?

 

 

I ran into this situation  before myself - the problem is with the database and, if you specify a query > a certain date without specifying a time (i.e. is the comparison vs. the beginning of the day, or the end of the day?).  Looks like this was just a hack work around.  A better solution IMO  is to have the code add a time of 0:00:00 (or something similar) to the date so it is no longer ambiguous.

DiamondDave:

...To query a date field over a range, the value of the query field must be exactly "{START_DATE}' OR {FIELD_NAME} BETWEEN '{START_DATE}' AND '{END_DATE}"....



what's the reason for this madness?

My guess:



It creates dynamic sql strings based on the XML params passed in, but
the XML format is so strict and poorly implemented that simple things
like selecting a range of dates is not possible (since the format of Field Name/Value doesn't allow for ranges or anything fancier than a Field=Value criteria).

The hard-coded sql is probably generated like this:

"Where Date = ' " + Value + "'"

So, essentially, this system *requires* performing Sql-injection to pull out the data needed.  And this is apparently the standard way to query the data, documented and everything!

DiamondDave:

...To query a date field over a range, the value of the query field must be exactly "{START_DATE}' OR {FIELD_NAME} BETWEEN '{START_DATE}' AND '{END_DATE}"....

what's the reason for this madness?

I'm guessing that it constructs a SQL statement based on the field and value by simply saying:

   where {field_name} = {value}

thus putting "between {start_date} and {end_date}" will result in:

   where {field_name} = between {start_date} and {end_date}

which obviously will not work. The proposed solution produces:

   where {field_name} = {start_date} or {field_name} between {start_date} and {end_date}

John Bigboote:

Me slow.

me slower

Anonymous:

Whenever anybody says how great XML is, I just remind myself that XML is just a text file format. So if you take out "XML" and replace it with "text files" it sounds much less exciting and revolutionary.

Or you could insist on using the proper acronym and calling it "EML".  EML doesn't sound as interesting as XML; maybe then it will lose some appeal...

ammoQ:

John Bigboote:

Me slow.

me slower

me slow too....

DiamondDave:

...To query a date field over a range, the value of the query field must be exactly "{START_DATE}' OR {FIELD_NAME} BETWEEN '{START_DATE}' AND '{END_DATE}"....



what's the reason for this madness?

That's basically a SQL WHERE clause.  Which is the WTF:  they're letting the user write raw SQL in the xml.  They could probably do something like:


'{START_DATE}' OR {FIELD_NAME} BETWEEN '{START_DATE}' AND '{END_DATE}' UNION SELECT password FROM users WHERE username = root; TRUNCATE TABLE sensitive_application_table;

merreborn:

DiamondDave:

...To query a date field over a range, the value of the query field must be exactly "{START_DATE}' OR {FIELD_NAME} BETWEEN '{START_DATE}' AND '{END_DATE}"....



what's the reason for this madness?

That's
basically a SQL WHERE clause.  Which is the WTF:  they're
letting the user write raw SQL in the xml.  They could probably do
something like:


'{START_DATE}' OR {FIELD_NAME} BETWEEN
'{START_DATE}' AND '{END_DATE}' UNION SELECT password FROM users WHERE
username = root; TRUNCATE TABLE sensitive_application_table;

Ah... it all makes sense now.  Apparently adhering to basic security rules wasn't a requirement for the EntityMisManager

Is it me or is the real WTF the '£1000/developer/day'?



I could quite happily sustain myself (and my degree) on about £5000 a
year. Just let me work hacking some ex-ML monster together for a week
and I'll be sorted!



Where do I sign?

Is that XMSQL or SQXML?  I can never tell the difference.  Should've use JavaVBScript instead.

I realize that the deeper significance of this snippet of code is that there are likely several million SQL injection attacks just waiting to swarm over this piece of software, that it's probably representative of even greater horrors lurking just beneath the surface, etc, etc.

But there's a part of me that can't help but think, "You know, this entire system was probably designed due to some know-nothing executive's decision that absolutely every last thing in the CRM be represented as XML, even though he has no idea what XML is or why it might be useful."  And then I think that he was probably perfectly happy with this result, because he has no idea why it's bad.  And then I think, some people get the code they deserve. And I'm happy again.

Anonymous:

Is it me or is the real WTF the '£1000/developer/day'?

What the conslutant charges the client and what the conslutant pays his developers are two entirely different things.  When I was young and niave and not too long out of school, I was making $36,000 a year.  My employer was charging me out to clients at $1000/day.   Quick, work out the profit margin on *that* little transaction.

merreborn:

DiamondDave:

...To query a date field over a range, the value of the query field must be exactly "{START_DATE}' OR {FIELD_NAME} BETWEEN '{START_DATE}' AND '{END_DATE}"....

what's the reason for this madness?

That's basically a SQL WHERE clause.  Which is the WTF:  they're letting the user write raw SQL in the xml.  They could probably do something like:


'{START_DATE}' OR {FIELD_NAME} BETWEEN '{START_DATE}' AND '{END_DATE}' UNION SELECT password FROM users WHERE username = root; TRUNCATE TABLE sensitive_application_table;

I concur.

Good WTF!

cconroy:

Is that XMSQL or SQXML?  I can never tell the difference.  Should've use JavaVBScript instead.

It's obviously SEXML, the database penetration and SQL injection tool for discriminating idiots everywhere!

You know "The Scream", that famous Heironymous Bosch painting that appears everywhere (including the cover of "The Unix Hater's Handbook")? 

I'm doing that right now.

I understand how crap like this happens.  You need to get a solution working, and doing it the Right Way would take more time/money/talent than you have available, so you throw up something simple and dirty and pray no one notices.

Even so, someone should get sued over that.

"Hello, I'm a security hole.  Please take advantage of me."

 

BtM:

You know "The Scream", that famous Heironymous Bosch painting

Bosch? Now that's a WTF for you.

BtM:

You know "The Scream", that famous Heironymous Bosch painting that appears everywhere (including the cover of "The Unix Hater's Handbook")? 

I'm doing that right now.

I understand how crap like this happens.  You need to get a solution working, and doing it the Right Way would take more time/money/talent than you have available, so you throw up something simple and dirty and pray no one notices.

Even so, someone should get sued over that.

"Hello, I'm a security hole.  Please take advantage of me."

 

s/Heironymous Bosch/Edvard Munch/

Anonymous:

BtM:

You know "The Scream", that famous Heironymous Bosch painting

Bosch? Now that's a WTF for you.

Yea, he said the wrong artist.  But to be honest, Bosch would be
appropriate here too.  "... and this paiting shows the developers
being cast into XML hell, note the demons injecting them with SQL
queries shapped like spears"

BtM:

You know "The Scream", that famous Heironymous
Bosch painting that appears everywhere (including the cover of
"The Unix Hater's Handbook")? 

I'm doing that right now.

I understand how crap like this happens.  You need to
get a solution working, and doing it the Right Way would take more
time/money/talent than you have available, so you throw up something
simple and dirty and pray no one notices.

Even so, someone should get sued over that.

"Hello, I'm a security hole.  Please take advantage of me."

The Real WTF (tm) is not that they have this kind of vulnerability in
their system. This kind of bug is unfortunately often made. The Real
WTF (tm) is that they are documenting it as a feature.
Should they ever fix it, they will have to include a workaround for
exactly this kind of search string, so to keep it compatible with
existing "queries".

Anonymous:

Anonymous:

BtM:

You
know "The Scream", that famous Heironymous Bosch painting

Bosch? Now that's a WTF for you.

Yea, he said the wrong artist.  But to be honest, Bosch would be
appropriate here too.  "... and this paiting shows the developers
being cast into XML hell, note the demons injecting them with SQL
queries shapped like spears"

SELECT * FROM artist aWHERE a.artist_name IN ('Edvard Munsch', 'Hieronymous Bosch')

OR a.genre_id = 'AAAGH_IT_BURNS_IT_BURNS'

ptomblin:

Anonymous:

Is it me or is the real WTF the '£1000/developer/day'?

What the conslutant charges the client and what the conslutant pays his developers are two entirely different things.  When I was young and niave and not too long out of school, I was making $36,000 a year.  My employer was charging me out to clients at $1000/day.   Quick, work out the profit margin on *that* little transaction.

We need more data.  What happened on days when you did not do any work for a clients?  What other expenses were there?  Sales/marketing, admin, office space, etc.?

Sincerely,

Gene Wirchenko

ptomblin:

Anonymous:

Is it me or is the real WTF the '£1000/developer/day'?

What the conslutant charges the client and what the conslutant pays his developers are two entirely different things.  When I was young and niave and not too long out of school, I was making $36,000 a year.  My employer was charging me out to clients at $1000/day.   Quick, work out the profit margin on *that* little transaction.

At the end of the day, did he come to you and say "Where my money, BEE-YATCH!? You holdin' out on me?"[;)]

Anonymous:

Yea, he said the wrong artist.  But to be honest, Bosch would be
appropriate here too.  "... and this paiting shows the developers
being cast into XML hell, note the demons injecting them with SQL
queries shapped like spears"

<While trying to get the rest of the milk out of my nose.>
Hightlight, Copy, Paste to favorite quotes.
File / Save.

I have changed my mind, this isn't a WTF at all.  It just a
standard implementation using the well known SQL Injection design
pattern.

I don't get the "xml-abuse" comment - there's nothing in this WTF that is dependent on, or even encouraged by, XML.   If I owned this code, knew nothing about SQL injection attacks, and my boss told me "no more XML!", I could reimplement it using a different text format, or COM, or .NET, and leave the security hole wide open.

BTW, if you had to maintain the API and fix the security hole, what would you do?   I can imagine trying to do it by just changing the backend code to submit the dangerous query under a user account with really restricted permissions.

ptomblin:

Anonymous:

Is it me or is the real WTF the '£1000/developer/day'?

What the conslutant charges the client and what the conslutant pays his developers are two entirely different things.  When I was young and niave and not too long out of school, I was making $36,000 a year.  My employer was charging me out to clients at $1000/day.   Quick, work out the profit margin on *that* little transaction.

Reminds me of when my company sent me to the states billing at $125 US / hour while still paying my regular salary in Canadian dollars. And then when you add the overtime hours that get billed to the client without any effect on my compensation, it must have worked out quite well. The lead developer put in 80 hours a week for two weeks straight billing at $125 / hour. The company bought him a T-shirt to recognize his hard work. The company also told us that expensing shampoo and soap was unethical.

R.Flowers:

ptomblin:

Anonymous:

Is it me or is the real WTF the '£1000/developer/day'?

What the conslutant charges the client and what the conslutant pays his developers are two entirely different things.  When I was young and niave and not too long out of school, I was making $36,000 a year.  My employer was charging me out to clients at $1000/day.   Quick, work out the profit margin on *that* little transaction.

At the end of the day, did he come to you and say "Where my money, BEE-YATCH!? You holdin' out on me?"[;)]

If he did I would have shot him in both knees and told him to get the money on Friday.

The correct way to deal with a SQL Injection vulnerability is to FIX IT, not write it up as an interface!!!

Value="1 ; shutdown --"

Anonymous:

I don't get the "xml-abuse" comment - there's nothing in this WTF that is dependent on, or even encouraged by, XML.   If I owned this code, knew nothing about SQL injection attacks, and my boss told me "no more XML!", I could reimplement it using a different text format, or COM, or .NET, and leave the security hole wide open.

True enough.  The WTF is cutting corners on a conversion between two data formats.

BTW, if you had to maintain the API and fix the security hole, what would you do?   I can imagine trying to do it by just changing the backend code to submit the dangerous query under a user account with really restricted permissions.

1) I would parse the query data and make very sure that anything submitted that way did fit the format.  If it did not, I would bounce it with an error message.  The spec already says that the data must follow the format exactly.

It is even possible that the spec is enforced now.  If so, this is not so much a WTF as lazy design.  I doubt this though as since the designer/coder got lazy one way, he may well have gotten lazy another way and not written code to enforce the spec.

2) Then, I would design another way of specifying date ranges.  The format would be friendlier, not requiring SQL knowledge.  I would make sure to parse everything fully.

3) Lastly, I would deprecate the first usage.  If I could do it, I would obsolete it.

Sincerely,

Gene Wirchenko

OneFactor:

ptomblin:

What the conslutant charges the client and what the conslutant pays his developers are two entirely different things.  When I was young and niave and not too long out of school, I was making $36,000 a year.  My employer was charging me out to clients at $1000/day.   Quick, work out the profit margin on *that* little transaction.

Reminds me of when my company sent me to the states billing at $125 US / hour while still paying my regular salary in Canadian dollars. And then when you add the overtime hours that get billed to the client without any effect on my compensation, it must have worked out quite well. The lead developer put in 80 hours a week for two weeks straight billing at $125 / hour. The company bought him a T-shirt to recognize his hard work. The company also told us that expensing shampoo and soap was unethical.

It was very similar for me.  The other WTF was that I was staying at a very expensive hotel in Madrid, where the hotel laundry charged 2500 Pesatas (about $2.50) to wash my underwear, but I could have bought new underwear for 2000 Pesatas ($2)/pr just down the street.  But the company wouldn't allow me to expense buying new underwear, even though it was a net savings for them.  So I came home with a lot of old underwear and socks with laundry tags on them, and a huge expense claim.

Anonymous:

Value="1 ; shutdown --"

ahh the sweet mayhem to be had by being allowed to inject my own sql statements through a hole in the API...

Value="1; drop database if exists main"

just cause I don't like people using the "main" database

cconroy:

Is that XMSQL or SQXML?  I can never tell the difference.  Should've use JavaVBScript instead.

 

They be richer now, new invention ... go and buy up the domain names so they will have to pay you

ptomblin:

It was very similar for me.  The
other WTF was that I was staying at a very expensive hotel in Madrid,
where the hotel laundry charged 2500 Pesatas (about $2.50) to wash my
underwear, but I could have bought new underwear for 2000 Pesatas
($2)/pr just down the street.

An enterprising businessman would buy both the laundry and the
underwear store, and then hire people to break into your hotel room and
crap in your underwear.

Folks seem to miss the subtle logic at work here:

1. Security flaws are worse because so many people are blissfully unaware of them.

2. Therefore: if more people knew about them, more people would know to avoid them.

3. Ergo: we should teach all users about SQL injection.

4. What better way to spread the word than a system that _operates by_ SQL injection. No-one can claim ignorance now!

Brilliant!

John Bigboote:

ptomblin:

It was very similar for me.  The other WTF was that I was staying at a very expensive hotel in Madrid, where the hotel laundry charged 2500 Pesatas (about $2.50) to wash my underwear, but I could have bought new underwear for 2000 Pesatas ($2)/pr just down the street.

An enterprising businessman would buy both the laundry and the underwear store, and then hire people to break into your hotel room and crap in your underwear.

Good plan, but I think I'll save money and do the crapping myself.

Anonymous:

BtM:

You know "The Scream", that famous Heironymous Bosch painting

Bosch? Now that's a WTF for you.

This episode of "Geek Bloopers and Practical Jokes" has been brought to you by an excess of caffeine, sugar, and UML.  Tune in next week when we see the geek blow a wedding toast by getting the bride's name wrong.