#1
  1. hiding my <b> from ur <strong>
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2004
    Posts
    959
    Rep Power
    181

    Most "common" form of securing queries


    I've typically either used php's built in functions, a custom function, or typecasting to secure my MySQL queries. Recently, however, I've seen more and more people using sprintf. I honestly don't care which method I use, and was just wondering if anyone knew what the current "industry standard" is (so it would look the most "professional" if someone were to a code review).

    Any thoughts?

    Thanks,
    Jeremy
    ****
    Enjoy my post? Drop some props by hitting the scales button up top. JBL

    Website Design in Los Angeles and Washington, DC by PoweredPages.com
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,920
    Rep Power
    1045
    Hi,

    shouldn't you be more interested in what's the most secure way? The answer to this is prepared statements.

    How to look cool in front of your code reviewers I don't know.

    Comments on this post

    • daprezjer agrees
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. hiding my <b> from ur <strong>
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2004
    Posts
    959
    Rep Power
    181
    For the limited purposes of this tightly controlled, particular project, there are many "secure" ways, and prepared statements, typecasting, etc. all in the end secure just as well.

    Of course, there's great debate on this everywhere:
    http://stackoverflow.com/questions/535464/when-not-to-use-prepared-statements

    But that same reference page also indicates that prepared statements are being used more often recently, and even in my tightly controlled environment, showing that I could use the "most" secure method in other projects is probably a boon.

    Thanks for the input.
    ****
    Enjoy my post? Drop some props by hitting the scales button up top. JBL

    Website Design in Los Angeles and Washington, DC by PoweredPages.com
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,920
    Rep Power
    1045
    Well, we've discussed the topic of "Why not just escape the values?" vs. prepared statements a lot lately.

    In my opinion, manual escaping has failed the field test. Over the last decades, thousands of developers have tried to make their queries secure by escaping the input. The result is disastrous. We've seen SQL injections again and again and again, not just with amateur software, but with big applications like Drupal, ownCloud and whatnot. They all have been vulnerable to SQL injections in the past, and they probably still are. It's just a matter of time when the next security hole will be found.

    In theory, there are many ways to protect your queries. In practice, none of them work except two: prepared statements and possibly query builders with automatic escaping. But applying an escaping function to every single input value of a query string seems to be beyond the capabilities of the human brain. Many people thought they could do it, but they've all failed sooner or later.

    And don't even start with stuff like type casting. If a simple approach -- escaping everything with the same function -- has already failed, the answer is not to make the approach more complicated.

    The stackoverflow discussion is mostly nonsense in my opinion. They're oh so worried about losing performance through prepared statements, but none of them has ever done a benchmark to check this claim. Shouldn't you at least know that prepared statements are actually the bottleneck of your application (which they aren't) before you sacrifice security for the sake of performance?

    Sorry for the rant. This has nothing to do with your question. I'm just getting sick of seeing the same stupid discussions and the same ignorance amongst "developers". Technology advances, but the people working with out seemingly don't.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. hiding my <b> from ur <strong>
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2004
    Posts
    959
    Rep Power
    181
    Hey it's a good rant. And I like your description of using other methods... "It works as a theoretical model, but it breaks down as soon as real people are involved..." Even if we think that we're perfect coders, if we ever expect our team to grow, you can't take that chance.

    Comments on this post

    • Jacques1 agrees
    ****
    Enjoy my post? Drop some props by hitting the scales button up top. JBL

    Website Design in Los Angeles and Washington, DC by PoweredPages.com
  10. #6
  11. hiding my <b> from ur <strong>
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2004
    Posts
    959
    Rep Power
    181
    You happen to have a good copy/paste example on hand of using PDO in a class-based environment, where you'd call, say a "inputRows" from a controller? I'm reinventing the wheel on my side a bit to get this to work, but figure since you have more experience with it I might as well take a look at your implementation, if you're willing.
    ****
    Enjoy my post? Drop some props by hitting the scales button up top. JBL

    Website Design in Los Angeles and Washington, DC by PoweredPages.com
  12. #7
  13. hiding my <b> from ur <strong>
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2004
    Posts
    959
    Rep Power
    181
    And do you happen to have a preference of PDO over MySQLi? I'm seeing various thoughts on this (http://net.tutsplus.com/tutorials/ph...hould-you-use/ ), but one things that seems missing in these discussions is the quickness of casting. I do think the ? marks do seem rather hacky, but the ability to say 'ss' instead of binding two parameters as 'PDO::PARAM_STR' seems appealing, if not unnecessary.
    ****
    Enjoy my post? Drop some props by hitting the scales button up top. JBL

    Website Design in Los Angeles and Washington, DC by PoweredPages.com
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,920
    Rep Power
    1045
    I definitely prefer PDO over MySQLi. The biggest advantage is that you're not bound to MySQL. So if you ever decide to switch to a real database system like PostgreSQL, you can do that without having to rewrite all your database code (you may have to rewrite some queries, of course).

    PDO is also much more convenient. You already mentioned the named parameters, but fetching from a prepared statement is also easier: You can simply loop over the PDOStatement like you do with normal queries. With MySQLi, you have to bind the result values and fetch the rows "manually".

    PDO:

    PHP Code:
    $test_stmt $database->prepare('
        SELECT
            x
            , y
        FROM
            test
        WHERE
            y <= :max
    '
    );
    $test_stmt->execute(array(
        
    ':max' => $max
    ));
    foreach (
    $test_stmt as $test_row) {
        echo 
    htmlspecialchars $test_row['x'] . ':' $test_row['y'], ENT_QUOTES ENT_XHTML'UTF-8'), '<br />';

    Pretty straightforward, right?

    The same with MySQLi:

    PHP Code:
    $test_stmt $database->prepare('
        SELECT
            x
            , y
        FROM
            test
        WHERE
            y <= ?
    '
    );
    $test_stmt->bind_param('s'$max);
    $test_stmt->execute();
    $test_stmt->bind_result($x$y);
    while (
    $test_stmt->fetch()) {
        echo 
    htmlspecialchars $x ':' $yENT_QUOTES ENT_XHTML'UTF-8'), '<br />';

    That's a lot of low-level fumbling with "bind" and "fetch".

    Note that I did not explicitly set the types with PDO. As far as I know, you never have to. MySQL generally doesn't care much about types. And when using a strongly typed DBMS like PostgreSQL, I'd do the type casting in the prepared statement itself.

    As to the MVC code you asked about in the previous post: Sorry, but I can't help you with that. I rarely use PHP, and I've never used a PHP MVC framework.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo