SQL injection as a feature



  • This:

     http://stackoverflow.com/questions/11546349/using-urldecode-results-in-mysql-error

     For those too lazy tofollow the link, here's the first line of the original question:

    "I need to pass special symbols through the URL for my MySQL query. For example, I need to have a URL that is something like: www.example.com/index.php?q=AND name LIKE '%hi%'"

     



  •  The correct answer is for him to provide a url and login to the relevant PHPMyAdmin to his client.



  •  Classic PHP. A best practice no doubt.



  • The reply correctly informs the OP not to do that, yet doesn't suggest using parameters! That's just stupid!

    Parameters are not only safer, but they're easier, too!!

    That's really just plain stupid.



  • @Xyro said:

    The reply correctly informs the OP not to do that, yet doesn't suggest using parameters! That's just stupid!

    Parameters are not only safer, but they're easier, too!!

    That's really just plain stupid.

    At least he says to use: mysql_real_escape_string($_GET['q'); that should escape character it just as if it was used as a parameter.

  • Trolleybus Mechanic

     Use a parameter! okbossthanks!

    @Pseudocode, so don't fuck at me about PHP and MySQL syntax said:

    $sql = " EXEC(?) "

    $params.add(DBType.VarChar, $_REQUEST["q"]);

    $db.exec($sql, $params);




  • @Xyro said:

    The reply correctly informs the OP not to do that, yet doesn't suggest using parameters! That's just stupid!

    So it's just like PHP's security recommendations page.

    @Xyro said:

    That's really just plain stupid.

    Yes, yes it is.



  • @Lorne Kates said:

     Use a parameter! okbossthanks!

    @Pseudocode, so don't fuck at me about PHP and MySQL syntax said:

    $sql = " EXEC(?) "

    $params.add(DBType.VarChar, $_REQUEST["q"]);

    $db.exec($sql, $params);


    Which PHP book did you plagiarise this from?

     


  • Trolleybus Mechanic

    @JimLahey said:

    @Lorne Kates said:

     Use a parameter! okbossthanks!

    @Pseudocode, so don't fuck at me about PHP and MySQL syntax said:

    $sql = " EXEC(?) "

    $params.add(DBType.VarChar, $_REQUEST["q"]);

    $db.exec($sql, $params);


    Which PHP book did you plagiarise this from?

     

     

     

    All of them?

     



  • @ASheridan said:

    This:

     http://stackoverflow.com/questions/11546349/using-urldecode-results-in-mysql-error

     For those too lazy tofollow the link, here's the first line of the original question:

    "I need to pass special symbols through the URL for my MySQL query. For example, I need to have a URL that is something like: www.example.com/index.php?q=AND name LIKE '%hi%'"

     

    It's a know pattern: it's called a RESTFool web service.



  • Fun fact: Unless GETTING random data is an exploit on this system, the mysql extension won't allow this to be an injection - it can only parse one command per method call.

    The mysqli extension, however, can parse multiple commands.



  • @Anketam said:

    At least he says to use: mysql_real_escape_string($_GET['q']); that should escape character it just as if it was used as a parameter.
    ... and if that still isn't safe enough he can always use mysql_really_escape_string_or_else_seriously_I_mean_it(...)


  • BINNED

    @Lorne Kates said:

     Use a parameter! okbossthanks!

    @Pseudocode, so don't fuck at me about PHP and MySQL syntax said:

    $sql = " EXEC(?) "

    $params.add(DBType.VarChar, $_REQUEST["q"]);

    $db.exec($sql, $params);


    Want a surprise? I can't get it to do that. At least not using PDO.

    //$pdo is a valid PDO object

    $stmt = $pdo->prepare("?");

    $stmt->bindValue(1, "SELECT * FROM customers", PDO::PARAM_STR);

    $result = $stmt->execute();

    Throws this:

    Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1' in /home/bnemcic/public_html/testing/pdo.php:9
    Stack trace:
    #0 /home/bnemcic/public_html/testing/pdo.php(9): PDO->prepare('?')
    #1 {main}
    thrown in /home/bnemcic/public_html/testing/pdo.php on line 9

    Score one for PHP refusing to parse this. Off to MySQL side, there's no EXEC statement, only EXECUTE, but to run it you have to pass it a prepared statement anyway. Out of curiousity I did try to feed a PREPARE statement into PDO but it barfed out an error about it not being supported. Which I don't fault it with, that should be only used in stored procedures anyway.

    So, I guess the lesson is to stop writing tutorials using mysqli since PDO is not any harder to use and is actually more readable since you get rid of all that string concateration.

    Disclaimer: I have no idea if you can make mysqli do this, nor do I care much at this point since I avoid it alltogether.



  • What exactly do you find surprising? Parameters are used to substitute values, not arbitrary query parts. Otherwise they would not protect against injection, would they?

    So, each one of the following "prepared statements" is invalid:

    SELECT * FROM ? WHERE ANSWER = 42
    SELECT ? FROM QUESTIONS WHERE ANSWER = 42
    SELECT * FROM QUESTIONS WHERE ANSWER ? 42 ;;hoping to replace with "=" or "<"

    Only the next one is correct:

    SELECT * FROM QUESTIONS WHERE ANSWER = ?


  • BINNED

    @IMil said:

    What exactly do you find surprising? Parameters are used to substitute values, not arbitrary query parts. Otherwise they would not protect against injection, would they?

    So, each one of the following "prepared statements" is invalid:

    SELECT * FROM ? WHERE ANSWER = 42
    SELECT ? FROM QUESTIONS WHERE ANSWER = 42
    SELECT * FROM QUESTIONS WHERE ANSWER ? 42 ;;hoping to replace with "=" or "

    I mostly wanted to see if someone can really exploit it in such an asinine way. I expected it to fail, and it did. I'm personally not surprised, but there's a running gag about php being hopelessly broken on this forum (which is, to a point, true I guess, but I never ran into any deal-breakers so far, apart from inconsistent function naming that is) so I went with it.

    Also, since I never took any php classes and learned everything by applying my existing "classic" ASP and C knowledge and reading tutorials / books, I found PDO relatively recently and haven't really had time to fiddle and try to exploit it (from programmer's perspective at least), so I wanted to see how it will deal with stupidity.

    Seriously, why isn't PDO used in online "begginer" tutorials more? By the time you find out about PDO you're already trained to automatically use mysqli, and then they present it like it's this huge and complicated beast at which point most people just turn away not wanting to waste any time on learning about it since mysqli does the job anyway.



  • @Onyx said:

    Seriously, why isn't PDO used in online "begginer" tutorials more?
     

    Have you compared the dates of such tutorials?

    Many online tutorials I've come across have never been updated and still contain legacy/deprecated/outdated methods. I'm glad of them datestamping the document, I'm annoyed they didn't see fit to keep it updated.



  • @IMil said:

    SELECT ? FROM QUESTIONS WHERE ANSWER = 42

    That one will sort of work, actually, but not the way you may want. It will return a constant value for each record of ANSWER = 42. As you suggest, it does not allow you to select an arbitrary column after the statement is prepared.

    I recently used something similar in order to return part of the inputs for a query that was called in batches. This was to help match things up after our pseudo-ORM ripped through the result set. Basically, the query was something like SELECT ?, myfunction(?) FROM DUAL, the return of which gave me both the input and the output of myfunction.


  • Trolleybus Mechanic

    @Onyx said:

    Want a surprise? I can't get it to do that. At least not using PDO.

    //$pdo is a valid PDO object

    $stmt = $pdo->prepare("?");

    $stmt->bindValue(1, "SELECT * FROM customers", PDO::PARAM_STR);

    $result = $stmt->execute();

     

    I was thinking more along the lines of:

    $sql = "SET @s = ?;"; // Equiv of saying "SET @s = 'DROP DATABASE';
    $sql .= "PREPARE stmt FROM @s;";
    $sql .= "EXECUTE stmt;";

    If MySQL doesn't do multiline, put that in a stored procedure. I don't have an MySQL database on hand to test, but that's the concept.

     


Log in to reply