How Do I: Prevent a SQL Injection Attack Over a PHP/MySQL/Linux Platform?



  • Ok guys,

    This is weird, I just found this video on the MSDN website: http://msdn2.microsoft.com/nl-nl/security/bb980214(en-us).aspx



  • @Ice^^Heat said:

    Ok guys,

    This is weird, I just found this video on the MSDN website: http://msdn2.microsoft.com/nl-nl/security/bb980214(en-us).aspx

    Do we need to watch the whole video for the WTF?

    Or are you talking about the preview image you have to click for the video?

    Or are you surprised that MS does work on Linux/LAMP as well?



  • @MasterPlanSoftware said:

    @Ice^^Heat said:

    Ok guys,

    This is weird, I just found this video on the MSDN website: http://msdn2.microsoft.com/nl-nl/security/bb980214(en-us).aspx

    Do we need to watch the whole video for the WTF?

    Or are you talking about the preview image you have to click for the video?

    Or are you surprised that MS does work on Linux/LAMP as well?

    I just think its odd that MS is hosting videos on LAMP.



  • @Ice^^Heat said:

    @MasterPlanSoftware said:

    @Ice^^Heat said:

    Ok guys,

    This is weird, I just found this video on the MSDN website: http://msdn2.microsoft.com/nl-nl/security/bb980214(en-us).aspx

    Do we need to watch the whole video for the WTF?

    Or are you talking about the preview image you have to click for the video?

    Or are you surprised that MS does work on Linux/LAMP as well?

    I just think its odd that MS is hosting videos on LAMP.

    I like it. I use LAMP and Mono all the time, but I also do a lot of customer stuff that is strictly MS. I would love to see more cooperation between the two parties. There is no reason there can't be.

    I have seen Joe Stagner speak, and have consulted with him on at least one occasion about an a project I was working on. He is a supporter of LAMP, and he is an MS employee, and a .NET guy. I think they call him an 'evangelist'. 

    He recently redid his blog, and I don't see anything recent on his blog about LAMP but have seen him blog about it on several occasions. He is a good resource if you are into either or both LAMP or .NET. I would like to see MS with more free thinkers like him.

    I have seen other MS bloggers talk about and use LAMP (yes, in positive ways) but I can't remember which ones off the top of my head.

    Edit: And actually Tom Hollander and his crew on the Patterns and Practices team helped get Enterprise Library working in Mono one time on a project I was involved in. That was kind of cool. Was not expecting to get a positive response, never mind actual help.



  • I watched most of that video... And it's quite a WTF in itself. I can barely hear what the guy is saying in the video... They need a new microphone or something.

    Anyways, basically, they write a simple PHP script to get a user's details when their ID is entered. The code is a mess, it has 4 levels of indentation, MySQL connection strings (with username and password) in the middle of the script itself, a "while" loop to get a single database record, and bad HTML ("header" tag?). With code like this, I hope that Microsoft never actually wrote any whole scripts in PHP. It's code you'd expect from a newbie PHP programmer (then again, whoever did the video is probably a PHP newbie?). Developers like this are what give PHP a bad name:

    (yeah, bad quality video, not sure whether it was the video itself or my PC)

    The script is meant to grab a single user, yet it has a while loop in it. They then go on to say that by entering something like 2 or 2=2 into the box, you can get a list of all the users in the database:

    This is partially due to the stupid script allowing this (with the while loop). Their solution, rather than validating the data at the top, was to wrap the whole code inside an if (is_numeric(...)) statement.

    At the end, he says that something like 1; delete from users; can be injected, however, I don't think this is possible. As far as I know, mysql_query() only allows one SQL query per call.



  • Notice also that they're connecting to the DB as 'root'. Guess the 'sa' mentality runs deep in Microsoft-land. I like the commented-out "is_numeric" check as well. Had that been left in, then their "2 or 2=2" bit would have failed and nuked the whole example.



  • @MasterPlanSoftware said:

    @Ice^^Heat said:

    I just think its odd that MS is hosting videos on LAMP.

    I like it. I use LAMP I love LAMP



  • @MarcB said:

    Notice also that they're connecting to the DB as 'root'.
    Not just that... look at the minimized Konsole window. They're running their entire Linux desktop as root!

    If this was Slashdot, by now at least eight people would have accused Microsoft of posting crap like this to try and make LAMP look like an amateurish and inherently insecure choice.



  • @Iago said:

    @MarcB said:
    Notice also that they're connecting to the DB as 'root'.
    Not just that... look at the minimized Konsole window. They're running their entire Linux desktop as root!

    If this was Slashdot, by now at least eight people would have accused Microsoft of posting crap like this to try and make LAMP look like an amateurish and inherently insecure choice.

    Ever heard of kdesu?

    Also, "root@localhost.localdomain"? Seems like there's some deliberate anonymisation going on there.

    TheRealWTFs are:

    1) "Sequel"

    2) An incredibly UGLY desktop. Now that has got to be intentional. Also note no syntax highlighting when KEdit is perfectly capable of doing it.



  • Holy crap this is bad.



  • @Daniel15 said:

    At the end, he says that something like 1; delete from users; can be injected, however, I don't think this is possible. As far as I know, mysql_query() only allows one SQL query per call.

    You're probably right. Why do you think he DOESN'T execute it? Does he really not want to have to retype the entries into his toy table? No, he knows it won't work, but wants to make LAMP look worse than it really is.



  • @m0ffx said:

    @Daniel15 said:

    At the end, he says that something like 1; delete from users; can be injected, however, I don't think this is possible. As far as I know, mysql_query() only allows one SQL query per call.

    You're probably right. Why do you think he DOESN'T execute it? Does he really not want to have to retype the entries into his toy table? No, he knows it won't work, but wants to make LAMP look worse than it really is.

    Surprisingly few people notice that line in the documentation, so it's not *unthinkable* he didn't realize that.

    Additionally, the code is highly ancient ($HTTP_POST_VARS has been deprecated for ages now, in favor of $_POST), so it's quite likely that at the time he learned PHP, that line wasn't there (maybe because the libraries didn't contain that feature at that time - it's not the PHP guys that set that restriction, they're just wrapping around MySQL AB's library). It's also a possible issue if you used the MySQLi extension and for whatever reason decided on mysqli_multi_query instead of mysqli_query (but if you're using MySQLi, then you should be using parameters instead).

    He also doesn't seem to touch upon string escaping (unless it's only mentioned in the video - only looked at the sample code), but I have a funny feeling he doesn't know about mysql_real_escape_string (the proper way of doing it if you can't use MySQLi.

    A good thing though, he does seems to know mysql_fetch_array results in poorer performance, although he uses the second parameter to avoid that instead of just calling mysql_fetch_assoc directly (even though that would have been shotrer and at least as readable). It's probably been quite a while since he learned anything new about PHP.



  • Additionally, the code is highly ancient ($HTTP_POST_VARS has been deprecated for ages now, in favor of $_POST)

    He's using MySQL 3.23, so I assume it's quite old (most likely PHP 3?).

    Why do you think he DOESN'T execute it? Does he really not want to have to retype the entries into his toy table?
    Exactly what I was thinking. He's really scared of losing 5 dummy entries :P
    1) "Sequel"

    I've seen a few people pronounce it like that... Am I the only one that says the letters ("S-Q-L")?

    In any case, I think using a while loop to retrieve a single record is a bigger WTF :P

    He also doesn't seem to touch upon string escaping (unless it's only mentioned in the video - only looked at the sample code)

    Nope, it's not mentioned.

    Edit: Oh boy, this is bad. If Microsoft's RSS feed (http://www.microsoft.com/feeds/msdn/en-us/HDI/Home-HDI.xml) has the correct date:

        <item>
          <title>How Do I: Prevent a SQL Injection Attack Over a PHP/MySQL/Linux Platform?</title>
          <description>In this how-to video, Remon Zakaria will explain and demonstrate the SQL Injection attack over a PHP/MySQL/Linux Platform and how can you protect yourself from it.</description>
          <link>http://msdn2.microsoft.com/en-us/security/bb980214.aspx</link>
          <pubDate>Tuesday, 18 Dec 2007</pubDate>
          <media:group>
            <media:content isDefault="true" url="http://download.microsoft.com/download/C/9/4/C94525A2-1632-44FD-A125-0D5589B53421/WinVideo-Security-SQLInjectionattackunderPHP-MySql-Linuxplatform.wmv" expression="full" medium="video" />
          </media:group>
          <media:thumbnail url="http://msdn2.microsoft.com/en-us/security/bb980214.Security-SQLInjectionattackunderPHP-MySql-Linuxplatform.jpg" height="85" width="64" />
          <enclosure url="http://download.microsoft.com/download/C/9/4/C94525A2-1632-44FD-A125-0D5589B53421/WinVideo-Security-SQLInjectionattackunderPHP-MySql-Linuxplatform.wmv" type="video/x-ms-wmv" />
          <category>Security</category>
        </item>
    

    18th December 2007? Now there's the real WTF, using PHP 3 code and MySQL 3.23 in 2007.



  • @Daniel15 said:

    1) "Sequel"

    I've seen a few people pronounce it like that... Am I the only one that says the letters ("S-Q-L")?

    We had this one recently. Sequel is the name of an ancient, now-obsolete product from the history of SQL. Anybody still saying it has their head rammed so far up their past that they're probably using EBCDIC. 



  • The developers at my current and former places of employment also pronounced it "sequel". Young guys (especially the former) who are barely aware of what ASCII is, much less EBCDIC. Possibly it was passed down through the ages though. Both places are Microsoft shops, which makes wonder if maybe there really is a Cult of Microsoft.  (Perth, .au)



  • @asuffield said:

    @Daniel15 said:
    1) "Sequel"

    I've seen a few people pronounce it like that... Am I the only one that says the letters ("S-Q-L")?

    We had this one recently. Sequel is the name of an ancient, now-obsolete product from the history of SQL. Anybody still saying it has their head rammed so far up their past that they're probably using EBCDIC. 

    To add the history, the original SQL language was named SEQUEL, however due to trademark problems, they had to make an new acronym for Structured English Query Language, and they settled on SQL.

    So, yes - SEQUEL is a predecesor to SQL, in much the same way as C is a predecesor to C++.



  • @Saxov said:

    So, yes - SEQUEL is a predecesor to SQL, in much the same way as C is a predecesor to C++.

    So they should have called it PREQUEL, then.
     



  • @m0ffx said:

    @Daniel15 said:

    At the end, he says that something like 1; delete from users; can be injected, however, I don't think this is possible. As far as I know, mysql_query() only allows one SQL query per call.

    You're probably right. Why do you think he DOESN'T execute it? Does he really not want to have to retype the entries into his toy table? No, he knows it won't work, but wants to make LAMP look worse than it really is.

     

    And he knows that it [i]would[/i] work on an MS SQL database. Maybe this is a "translation" of an existing MS presentation into (poorly written) LAMP-speak?

     



  • @asuffield said:

    @Daniel15 said:
    1) "Sequel"

    I've seen a few people pronounce it like that... Am I the only one that says the letters ("S-Q-L")?

    We had this one recently. Sequel is the name of an ancient, now-obsolete product from the history of SQL. Anybody still saying it has their head rammed so far up their past that they're probably using EBCDIC. 

    I thought we decided on "Squirrel."

    Also, it's just about split at my workplace between calling it "SQL" and "Sequel."  Same for my college professors. 

    I pretty much thing of it at "Line-ooks" versus "Linn-ux" versus "Line-ux" ... it doesn't fucking matter what you call it.  Do you know how to use it?  Or are you just "ideating?"



  • @belgariontheking said:

    @asuffield said:
    @Daniel15 said:
    1) "Sequel"

    I've seen a few people pronounce it like that... Am I the only one that says the letters ("S-Q-L")?

    We had this one recently. Sequel is the name of an ancient, now-obsolete product from the history of SQL. Anybody still saying it has their head rammed so far up their past that they're probably using EBCDIC. 

    I thought we decided on "Squirrel."

    Also, it's just about split at my workplace between calling it "SQL" and "Sequel."  Same for my college professors. 

    I pretty much thing of it at "Line-ooks" versus "Linn-ux" versus "Line-ux" ... it doesn't fucking matter what you call it.  Do you know how to use it?  Or are you just "ideating?"

     

    These are wise words, Sir  belgariontheking.

     You nerds should chill out and go play Warcraft or something.
     



  • @belgariontheking said:

    @asuffield said:
    @Daniel15 said:
    1) "Sequel"

    I've seen a few people pronounce it like that... Am I the only one that says the letters ("S-Q-L")?

    We had this one recently. Sequel is the name of an ancient, now-obsolete product from the history of SQL. Anybody still saying it has their head rammed so far up their past that they're probably using EBCDIC. 

    I thought we decided on "Squirrel."

    The problem with "Squirrel" is there's no 'r' in SQL.

    I like "Squeal". Calls to my mind a picture of exasperated developers ;-)



  • @m0ffx said:

    @belgariontheking said:
    @asuffield said:
    @Daniel15 said:
    1) "Sequel"

    I've seen a few people pronounce it like that... Am I the only one that says the letters ("S-Q-L")?

    We had this one recently. Sequel is the name of an ancient, now-obsolete product from the history of SQL. Anybody still saying it has their head rammed so far up their past that they're probably using EBCDIC. 

    I thought we decided on "Squirrel."

    The problem with "Squirrel" is there's no 'r' in SQL.

    I like "Squeal". Calls to my mind a picture of exasperated developers ;-)

    I like Pronouncing SQL as Ess Que Elle, mainly because there are no other letters in it's name.


  • @Lingerance said:

    @m0ffx said:
    @belgariontheking said:
    @asuffield said:
    @Daniel15 said:
    1) "Sequel"

    I've seen a few people pronounce it like that... Am I the only one that says the letters ("S-Q-L")?

    We had this one recently. Sequel is the name of an ancient, now-obsolete product from the history of SQL. Anybody still saying it has their head rammed so far up their past that they're probably using EBCDIC. 

    I thought we decided on "Squirrel."

    The problem with "Squirrel" is there's no 'r' in SQL.

    I like "Squeal". Calls to my mind a picture of exasperated developers ;-)

    I like Pronouncing SQL as Ess Que Elle, mainly because there are no other letters in it's name.

    I tend to vary randomly. There are a few words / terms like that where there are multiple pronunciations (e.g. linux, data, SQL, schedule, etc) that I end up speaking them the same way as the person I'm talking to.

    bah, so I'm a parrot. SQUAWK!  ;)




  • @Lingerance said:

    I like Pronouncing SQL as Ess Que Elle, mainly because there are no other letters in it's name.

    That makes me ponder an important question: how do you pronounce SCSI and <font face=Courier>strpbrk</font>? (Because I really pronounce them as S-C-S-I and strpbrk.)

    @belgariontheking said:

    I pretty much thing of it at "Line-ooks" versus "Linn-ux" versus "Line-ux" ... it doesn't fucking matter what you call it. Do you know how to use it? Or are you just "ideating?"

    [url]http://www.eu.kernel.org/pub/linux/kernel/SillySounds/english.au[/url] 9=]



  • @Spectre said:

    @Lingerance said:
    I like Pronouncing SQL as Ess Que Elle, mainly because there are no other letters in it's name.

    That makes me ponder an important question: how do you pronounce SCSI and <font face=Courier>strpbrk</font>? (Because I really pronounce them as S-C-S-I and strpbrk.)


    SCSI and String Pointer Break (guessing) respectively. I will pronounce an acronym only if it is actually pronounceable otherwise I will say each letter individually. Whereas I will usually unroll an abbreviation, which strpbrk is. Although I would not constantly say "String Pointer Break", after the first instance I would refer to it as a "string cut" or something easier to say repetitively.


  • Discourse touched me in a no-no place

    @Spectre said:

    @Lingerance said:
    I like Pronouncing SQL as Ess Que Elle, mainly because there are no other letters in it's name.
    That makes me ponder an important question: how do you pronounce SCSI and <FONT face=Courier>strpbrk</FONT>? (Because I really pronounce them as S-C-S-I and strpbrk.)

    I pronounce them as SCSI and strpbrk. I'm sure I mentioned Feathersonhaugh on the last thread about squeel. I pronounce that as it is.

    This isn't helping I'm sure.



  • @Spectre said:

    That makes me ponder an important question: how do you pronounce SCSI and <font face="Courier">strpbrk</font>? (Because I really pronounce them as S-C-S-I and strpbrk.)

    scuzzy and strip bark 


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.