• God (unregistered)

    XML blows.


  • (cs)
    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 ... -->
    <!--</SPAN-->Fields>
  <!--</SPAN-->Entity>
  <!-- ... More Entities ... -->
<!--</SPAN-->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"
          />
    <!--</SPAN-->Fields>
  <!--</SPAN-->Entity>
<!--</SPAN-->EntityQuery>


    Any chance this was designed by the same creators like the WMF file format?
  • (cs) in reply to ammoQ

    public class DropTableCommand : EntityQuery
    {
        //TODO: implement
    }

  • Bob (unregistered)

    {COMMENT_TEXT}

  • (cs)

    ...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?



  • YAYitsAndrew (unregistered) in reply to DiamondDave

    xml!

    run!

  • (cs)

    ahh.... xml-abuse!

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

  • (cs) in reply to DiamondDave

    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.

  • (cs)

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

    </sarcasm>
  • John DeHope 3 (unregistered) in reply to H23

    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.

  • (cs) in reply to DiamondDave
    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.
  • (cs) in reply to John Bigboote

    Me slow.

  • (cs) in reply to DiamondDave
    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.

  • ChiefCrazyTalk (unregistered) in reply to DiamondDave

    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.

  • (cs) in reply to DiamondDave
    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!
  • (cs) in reply to DiamondDave

    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}

  • (cs) in reply to John Bigboote
    John Bigboote:
    Me slow.

    me slower
  • (cs) in reply to John DeHope 3
    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...
  • (cs) in reply to ammoQ
    ammoQ:
    John Bigboote:
    Me slow.

    me slower


    me slow too....
  • (cs) in reply to DiamondDave
    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;
  • (cs) in reply to merreborn
    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
  • Mark (unregistered)

    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?

  • (cs)

    <font size="2">Is that XMSQL or SQXML?  I can never tell the difference.  Should've use JavaVBScript instead.
    </font>

  • (cs)

    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.

  • (cs) in reply to Mark
    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.

  • Randolpho (unregistered) in reply to merreborn

    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!

  • mjan (unregistered) in reply to cconroy
    cconroy:
    <font size="2">Is that XMSQL or SQXML?  I can never tell the difference.  Should've use JavaVBScript instead.
    </font>


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

    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."

     

  • Stern (unregistered) in reply to BtM
    BtM:
    You know "The Scream", that famous Heironymous Bosch painting
    Bosch? Now that's a WTF for you.
  • Onan (unregistered) in reply to BtM
    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."

     




    <font face="arial,sans-serif" size="3">s/</font><font size="3">Heironymous Bosch</font><font face="arial,sans-serif" size="3">/Edvard Munch/</font>
  • Runtime Error (unregistered) in reply to Stern
    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"


  • (cs) in reply to BtM
    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".
  • (cs) in reply to Runtime Error
    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'
  • (cs) in reply to ptomblin
    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

  • (cs) in reply to ptomblin

    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?"[;)]

  • (cs) in reply to Runtime Error
    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.

  • Runtime Error (unregistered) in reply to Gene Wirchenko

    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.

  • Tom (unregistered) in reply to H23

    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.

  • (cs) in reply to ptomblin

    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.

  • (cs) in reply to R.Flowers
    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.
  • (cs)

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

  • Dan (unregistered)

    Value="1 ; shutdown --"

  • (cs) in reply to Tom
    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

  • (cs) in reply to OneFactor
    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.


  • (cs) in reply to Dan
    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
  • (cs) in reply to cconroy

    cconroy:
    <FONT size=2>Is that XMSQL or SQXML?  I can never tell the difference.  Should've use JavaVBScript instead.
    </FONT>

     

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

  • (cs) in reply to ptomblin
    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.
  • (cs)

    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!

  • (cs) in reply to John Bigboote

    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.

  • (cs) in reply to Stern

    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. 

Leave a comment on “Solution Injecting”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article