[thelist] Single and double quotes - forms and mysql

[Simon MacDonald]

Renoir,

Thanks for this example, I've been considering reworking some my code to use PDO - You've convinced me.
Very useful post.

Regards

Simon

Simon MacDonald
simonmacdonald at uk2.net
07966 906 799


-----Original Message-----
From: thelist-bounces at lists.evolt.org [mailto:thelist-bounces at lists.evolt.org] On Behalf Of Renoir B.
Sent: 24 September 2013 15:27
To: bobm at dottedi.biz; thelist at lists.evolt.org
Subject: Re: [thelist] Single and double quotes - forms and mysql

Hello Bob,

I might sound like a Duty call intervention [6], but I think you might see what I mean by the following sample.

I know what you are doing and what I am saying is that you are using a 10+ y. old technique that is deprecated.

My recommendation is to use PDO and/or PHP's filter functions [1][2], but please, not mysql_real_escape_string as it *IS** deprecated* (look at the notice on top of the page) [0].

Would do much better than using the deprecated mysql_real_escape_string and a concoction of  str_replace.  Also that it ignores UTF-8 characters and you would have to use mb*replace [7].

Some people might want to insert a U+1F4A9 [8] character someday in their comment.

Therefore, as I was recommending (with code samples):


*Use PHP native filters*

    <?php
    $title = filter_input(INPUT_POST,
$_POST['form']['title'], FILTER_SANITIZE_STRING);




*And/Or using PDO:*

I am not sure if PDO escapes strings already with the quote() [9]  method, but the prepare() [10] method is taking care of it.

    <?php
    /* Initialize, see http://www.php.net/manual/en/pdo.construct.php [5] */
    $conn = new PDO('mysql:dbname=testdb;host=127.0.0.1', $dbuser, $dbpass);

    try {
        $sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour');
        // See the third argument, it is a PDO::PARAM_* constant :
        //    - http://php.net/manual/en/pdostatement.bindparam.php  [3]
        //    - http://www.php.net/manual/en/pdo.constants.php  [4]
        $sth->bindParam(':calories', $_POST['calories'], PDO::PARAM_INT);
        $sth->bindParam(':colour', $_POST['colour'], PDO::PARAM_STR, 12);
        $sth->execute();
    } catch(Exception $e) {
        // Ensure that it is not commited by rolling back the query
        $conn->rollBack();
        // Assuming this block is not in a view, it should grab this throw, from an other try..catch block where this block is called from
        throw $e;
    }



*Field association*
*
*
Also, I see that you seem to hardcode field association with the database.

The best is to also use form as an array (e.g. fields withname="register[title]",
name="register[fname]") and have either a mapping to get to/from the database.

Then you could create a mapping of each field and their type (defining the appropriate PDO::PARAM_INT). Then loop from the $_POST['form'] fields array and add data from the mapping.

Finaly, run $sth->bindParam() on each of them.

But I just described what Doctrine2 does in part. :]


*Finally...*

Hope this applied sample connected dots for you :)


  [0]: http://php.net/manual/en/function.mysql-real-escape-string.php
  [1]: http://www.php.net/manual/en/filter.filters.php
  [2]: http://www.php.net/manual/en/function.filter-input.php
  [3]: http://php.net/manual/en/pdostatement.bindparam.php
  [4]: http://www.php.net/manual/en/pdo.constants.php
  [5]: http://www.php.net/manual/en/pdo.construct.php
  [6]: http://xkcd.com/386/
  [7]: http://php.net/manual/en/function.mb-ereg-replace.php
  [8]: http://www.fileformat.info/info/unicode/char/1f4a9/index.htm
  [9]: http://php.net/manual/en/pdo.quote.php
  [10]: http://www.php.net/manual/en/pdo.prepare.php


Regards,

*Renoir Boulanger * |  Frontend & Software developer https://renoirboulanger.com/ <https://renoirboulanger.com/#is>  ✪ @renoirb<https://github.com/renoirb>
~
-- 

* * Please support the community that supports you.  * * http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester and archives of thelist go to: http://lists.evolt.org Workers of the Web, evolt !