Unique key constraints



  • I've been working with a friend on some web development in my free time. Mostly we try to work on larger, long-term projects (6-12 months), but he still has contacts with lots of smaller companies that will occasionally call up and ask us to fix something that's horribly broken and they need it working NOW.

     As you might have already guessed, these smaller companies had previously hired complete jokers who were clueless, and most of the applications are written in PHP -- the language most notorious for reinforcing the maxim, "A little knowledge is a dangerous thing" -- and hooked to a MySQL or SQL Server backend.

     Now, I'm used to finding SQL injection and XSS vulnerabilities; I'm also used to poorly organized code, ill-conceived data model, etc.. Those things don't ever merit an example anymore.

     But last night I helped out a small academic organization who was having problems with certain members logging in. Here's what the code looked like, in brief:

    $result = query(" select * from admin where username='$username' and password='$password' ");
    

    if (num_rows($result) == 1) {
    // do admin login
    } else {
    // it's not an admin, maybe its a regular user?
    $result = query(" select * from entities where username='$username' and password='$password' ")
    if (num_rows($result) == 1) {
    // do regular login
    } else {
    print "you suck at life";
    }
    }

    Ignoring the fact that there are two seperate "user" tables (the admin table actually just has one row with "admin"/"admin" as user/password), and that the main users table is inexplicably called "entities" (no table should ever be called entities... unless maybe you were data modeling the storage of a data modeling program...even then, I'm sure there's a better name), this code was repeatedly printing "you suck at life" (actual error message anonymized) for a handful of users.

    Expecting there to be some weird text encoding issue (the designer of this site never used the LIKE operator, not even once) that was causing the login query to return 0 rows, I ran an ad hoc query against the entities table and instead saw several rows returned.

     "That's weird," I thought to myself. "That could only happen if two people had the same username... and the same password." MySQL supports self joins, so I ran a query to see if usernames were repeated anywhere. Sure enough, 63 usernames were used more than once. Out of those, there were 5 repeated usernames that also had the same password! 

    If I had to guess, the original author of this differently-abled website imported a user list from some unknown source <cough>Excel</cough>, then populated a default username and password as LOWER(FirstName). This led to three Davids and two Marks having identical login credentials; they were unable to change their password since they were never able to login either. I suppose the designer's first name was not Mark or David, as that would have quickly revealed a defect in his design -- if he had ever actually tested it.

    Unlike the previous programmer, I'm a total badass, so I reset all usernames to CONCAT(FirstName, LastName) (using another ad hoc query on the production server, obviously) and then enabled a UK on the username column.

    One of the other bugs I fixed this weekend involved users with the last name O'Connor and O'Sullivan not being able to login. I'll let you guess what the fix was for that. I don't mind working on crap like this--I really don't. I just wish there was somebody in the client's organization who could possibly empathize with how completely awful and evil the last programmer was...somebody who would speak up the next time they hire a $10/hr student programmer and insist on hiring somebody with real experience.



  • @savar said:

     "That's weird," I thought to myself. "That could only happen if two people had the same username... and the same password." MySQL supports self joins, so I ran a query to see if usernames were repeated anywhere. Sure enough, 63 usernames were used more than once. Out of those, there were 5 repeated usernames that also had the same password! 

    Representative quote.

    I also love how the OP made no mention of salt or hash browns.   I presume the site used neither. 

    Of those 63 (or 5), how many passwords were 'secret' or 'password?'
     



  • @belgariontheking said:

    @savar said:

     "That's weird," I thought to myself. "That could only happen if two people had the same username... and the same password." MySQL supports self joins, so I ran a query to see if usernames were repeated anywhere. Sure enough, 63 usernames were used more than once. Out of those, there were 5 repeated usernames that also had the same password! 

    Representative quote.

    I also love how the OP made no mention of salt or hash browns.   I presume the site used neither. 

    Of those 63 (or 5), how many passwords were 'secret' or 'password?'
     

    MMMM hash browns.....



  • @savar said:

    A little knowledge is a dangerous thing

    "Stand back!  I've got knowledge and I'm not afraid to use it!"

     re: salt - I inferred from the OP that passwords were in plaintext - so no, no salt...  though everyone knows that salt is bad for you.



  • @GalacticCowboy said:

    @savar said:

    A little knowledge is a dangerous thing

    "Stand back!  I've got knowledge and I'm not afraid to use it!"

     re: salt - I inferred from the OP that passwords were in plaintext - so no, no salt...  though everyone knows that salt is bad for you.

    Yes, but salted hashbrowns are delicious.



  • @savar said:

    One of the other bugs I fixed this weekend involved users with the last name O'Connor and O'Sullivan not being able to login. I'll let you guess what the fix was for that.

    Obviously it was to turn on 'magic_quotes_gpc'. It's the ultimate in PHP data security, you know.
     



  • No, php 6 will drop that (with safemode and register globals), it will break. Not to mention the unicode issues.



  • The biggest O'Connor hack I've ever seen replaced all ' with ` on inserts and then replaced all ` with ' on SELECTS.

    I'm not kidding.


     



  • @Jonathan Holland said:

    The biggest O'Connor hack I've ever seen replaced all ' with on inserts and then replaced all with ' on SELECTS.

    I'm not kidding.

    I would have replaced each ' with: robert'); DROP TABLE Students; --

     



  • TheRealWTF here is I was about to ask what the heck are UKs despite the post's title.



  • @MasterPlanSoftware said:

    @belgariontheking said:
    @savar said:

     "That's weird," I thought to myself. "That could only happen if two people had the same username... and the same password." MySQL supports self joins, so I ran a query to see if usernames were repeated anywhere. Sure enough, 63 usernames were used more than once. Out of those, there were 5 repeated usernames that also had the same password! 

    Representative quote.

    I also love how the OP made no mention of salt or hash browns.   I presume the site used neither. 

    Of those 63 (or 5), how many passwords were 'secret' or 'password?'
     

    MMMM hash browns.....

     I'll pass on the hash browns but the salt will go well with my lunch!



  • The best one I've seen replaced ' with !~~! on insert or update and then reversed it on select.  Because of the way the function was used, you couldn't replace the internals with the actual API escape function because the same data was frequently "escaped" by the custom function several different times.  Obviously that works with a simple replace but you can't escape the same data twice.  This was actually considered a feature, the theory being that you didn't have to worry if the data was already escaped, you could just escape it again safely.  Of course, this meant that lots of data made it into queries without being "escaped" by the custom function.  The programmer who architected was the worst developer I have ever known.  Now he works for a second-tier search company.



  • @belgariontheking said:

    Representative quote.

    I also love how the OP made no mention of salt or hash browns.   I presume the site used neither. 

    Of those 63 (or 5), how many passwords were 'secret' or 'password?'
     

    Correct. There was no salt, and passwords were all stored in plaintext. (That's how I figured out that the default password was the user's last name.)

    But I wouldn't even expect something like that from somebody who couldn't find the need to escape quotation marks in query parameters, or check to see if a username is already used before issuing a new one.



  • Your solution was also a WTF: 

    Suppose there's an existing user with the login / password combo:

    sarahm / alley

    A new user tries to register with the following login / password combo

    sarah / malley

    .. That would result in either logging in 'sarahm' or print("you suck at life").  ...

    EDIT: unless you left the password column alone. just thought of that.



  • @Jonathan Holland said:

    The biggest O'Connor hack I've ever seen replaced all ' with on inserts and then replaced all with ' on SELECTS.
    I use a site that does this regularly - not only on selects, but on inserts and updates. The WTF is, that the site is coded in Perl, which had parametric queries practically forever (then again, the site is being rewritten, and the developers keep mentioning how insane the old code is).


Log in to reply