Emailed SQL Statements



  •  I think the title says it all but I will elaborate. 

    I was given the task to add a PayFriend "Pay Now" button. To a "very simple" php page. I made an over-estimate of 6 hours to complete the task. All I knew is that the purpose of the page was to sign up for an event, if you are a student you could recieve a discount and a MySQL database is involved -- how bad could it be? 

    Turns out pretty darn bad.  

    Well the database was total feces. There were many tables, and the tables were interlinked with each other. But only two table were actually ever used (through out the entire webpage. Most features were turned off - perhaps because they didnt work) - the in use tables consisted of a user table, an order table. Unfortunatly it didn't occur to me to check to see which tables were actually used by the system until late in the day. And even then a quick grep told me that every table was called on almost every page.  It wasn't until I got down into the nitty-gritty log-in feature that I realized that these extra tables were not actually in use.

    The user table held an email address, a boolean spam flag, a company name text field, and a paid boolean flag. The order table held a primary key a user_id  column, and a boolean paid flag.

    To place an order you first specify the event to attend, then specify the number of people you are signing up, Next the names, companies and is_student values for each user. Lastly you confirm the order.

    The way it actually worked was like hell on earth.

    After selecting an event you are redirected to /page.php?event_id=123&admin=0
    Next the page checks if you are logged in (there is no log in page setting admin=1 specifies the same php code in a seperate block)


    Please note that I cleaned up this code just to illustrate the major wtf's and avoiding many of the landmines.

    if($admin) {
    //code ABC
    } else {
    //code ABC
    }
    I suppose that code was removed and the thought of     if( false )     never occurred to the original programmer.

    Next if the first of the user names not yet submitted does not exist then the number of students to add is asked:

    if( $names[0] == '' ){

    ?>   <select><option>1</option><.... yes these were written out... ></select>    <?

    }

    Next the page checks to see if each company name has already been inserted into the user table. If this is true then an error message is displayed and the page will not continue. Perhaps the logic of this feature was designed to keep only a single person from any company to attend any event. Or perhaps they wanted unique company names so they could be used as a primary key later (seriously). Or perhaps the programmers came straight from hell and were just into sick jokes like that. I'm still not sure.


    The rest of the flow of logic worked mostly in this way. If the something has a null value (or pre-determined value) then present the user with the html prompting for that thing otherwise put that value in a hidden field. The html would just be inserted where ever the php code may lie. perhaps the php code was echo'ed, or perhaps stored in a variable and echoed later. Ironiclly the hidden fields end position were very predictable. 


    At some point the user is given the price(s) s/he is expected to pay. That logic worked similar to the following mess:

    students15 + graduates0 + adults*30

    The database did not store the price quoted to the user. And it was impossible to determine what the quoted price was later because of the wtf that follows.

    Next the user is asked to confirm everything. At this point the system shoots off an email. The email is similar to:

    INSERT INTO users (...) values (...);INSERT INTO users (...) values (...);INSERT INTO users (...) values (...);

    INSERT INTO orders (user_id, paid) VALUES ($user_id, true);INSERT INTO orders (user_id, paid) VALUES ($user_id, true);INSERT INTO orders (user_id, paid) VALUES ($user_id, true);

    This was emailed directly to the last developer (hard-coded email address in the page) and stored in a log.

    After this point the developer could create a custom static html page. Doing a select * from the users table and dumping that to a text file, and then with a little copy and paste magic a html page was created. This would have a list of every user in the system. Each represented with a button with their name on it.  If the button is clicked (and if  the sql command was present) the user's purchase would be recorded. This button was clicked at the entrance of the event after the user paid. 

     



  •  Wow, it's hard to keep track of all the WTFs. I can't decide what is the worse thing there. Have you been able to 'optimize' some of the code? 



  • @yshiphuf123 said:

    At some point the user is given the price(s) s/he is expected to pay. That logic worked similar to the following mess:

    students*15 + graduates*0 + adults*30

    There's not much I can say say about the rest, but I don't really see the WTF here. What am I missing?


  • @Zecc said:

    @yshiphuf123 said:

    At some point the user is given the price(s) s/he is expected to pay. That logic worked similar to the following mess:

    students*15 + graduates*0 + adults*30

    There's not much I can say say about the rest, but I don't really see the WTF here. What am I missing?

    The cost is hardcoded and no  real history of what he/she requested is kept. TRWTF though (if I'm not mistaken) is that the prices are not easy to check on the server so the user can create a page that lets him pay whatever he/she wants.



  • Well, look on the bright side, SQL injection would have been pretty difficult.



  •  Ha, that would be true.

    about  student*15 + graduates*0 + adults*30

    The prices were hard coded. The values were only echoed and never stored: 

     Price: <?= student*15 + graduates*0 + adults*30 ?>

    The variable graduates exists and is used only once on the page. (same line). Perhaps in verions 0.9 they used to prompt the user for that info.  (perhaps using a custom built tri-state checkbox) 



  • Hm... I should mail

    DELETE FROM Orders

    and test this "security".



  •  @danixdefcon5 said:

    Hm... I should mail

    DELETE FROM Orders

    and test this "security".

     

    If you can find the page, you don't even need to know his email address. Just go use SQL injection on the orders page. Just make up a very creative name for yourself. >:)



  • Now this is a Real WTF.  I haven't seen anything this good in the Sidebar for quite awhile.  Thanks for sharing!



  • @yshiphuf123 said:

    The prices were hard coded. The values were only echoed and never stored: 

     Price: <?= student*15 + graduates*0 + adults*30 ?>

    I've seen something similar in the payroll module of a large multi-million-dollar enterprise system.  Certain critical information is only available in the week that the payroll is paid, which has the result that you can't see what past payrolls actually paid the employees for.  You could only get totals.  We eventually worked around it by getting they payroll clerks to run a particular report after each pay run, which got FTP'd, SAMBA'd, converted from fixed-width to CSV via Excel, saved into an access database, back into Excel as a pivot table and saved to a public drive with only an Excel password for security.  It didn't seem like a WTF at the time.



  • That is indeed an excellent WTF, and deserves to show up as a best of the sidebar feature article.

    However, you do know it's incredibly sad to rate (both of) your own threads as 5 star, don't you? :p



  • @Zagyg said:

    However, you do know it's incredibly sad to rate (both of) your own threads as 5 star, don't you? :p
     

    Indeed, we have a lot of sad OPs like that lately.


Log in to reply