[thelist] Single and double quotes - forms and mysql

Renoir B. renoirb at gmail.com
Tue Sep 24 09:27:10 CDT 2013


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>
~


More information about the thelist mailing list