List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:September 19 2011 10:48pm
Subject:Re: Quotes around INSERT and SELECT statements' arguments from the
mysql CLI and PHP
View as plain text  

Am 20.09.2011 00:39, schrieb Dotan Cohen:
> On Tue, Sep 20, 2011 at 01:11, Hank <heskin@stripped> wrote:
>> Best of both worlds:
>>> $username=$_POST['username'];
>>> // do some stuff with username here
>>> $M=array();  // Array of things to be inserted into MySQL
>>> $M[username]=mysql_real_escape_string($username); // Everything that
>>> goes into $M is escaped
>>> $query="INSERT INTO table (username) VALUES ('{$M[username]}')";
>>>
>>>
>> I'm not sure I'm seeing why, in particular, you are using an array here?
>>
> 
> I want to be sure that all variables in the query are escaped. I don't
> trust myself or anyone else to do this to every variable right before
> the query:
> $someVar=mysql_real_escape_string($someVar);
> 
> Furthermore, I don't want to clutter the query with
> mysql_real_escape_string() all over the place. Therefore, I escape
> everything before it goes into the array, so I know that all the data
> in the array have been escaped. I can then use the array members in
> the query

i would use a samll class holding the db-connection with insert/update-methods
pass the whole record-array, lokk what field types are used in the table
and use intval(), doubleval() or mysql_real_escape-String

so you never write "insert into" inline and if the function is well desigend you
can throw the whole $_POST to it without thinikng about datatypes and ignore
automatically hidden-fields which are not used in the database

having as simple class with $db->fetch_all(), $db->insert, $db->update
has also the benefit that you can easy switch between mysql/mysqli
without the big overhead of a whole abstraction-layer and extend
this class with often used methods to make development faster
and much more stable as dealing the whole time with inline code

a basic class is written in few hours and can be extended whenever
needed - i wrote one ten years ago and heavily use it these days
as all the years

public function insert($table, array $data)
{
 // so here you know where to look for fieldnames/fieldtypes
 // prepare the data aray with escaping/intval()/doubleval()
 // and generate finally the insert
 //
 // as return value use 0 on errors or the insert-id
}


Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
Thread
Quotes around INSERT and SELECT statements' arguments from the mysqlCLI and PHPDotan Cohen18 Sep
  • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPBrandon Phelps18 Sep
    • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPDotan Cohen18 Sep
      • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPHank19 Sep
        • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPReindl Harald19 Sep
          • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPDotan Cohen19 Sep
          • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPHank19 Sep
            • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPReindl Harald19 Sep
              • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPDotan Cohen19 Sep
                • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPHank20 Sep
                  • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPDotan Cohen20 Sep
                    • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPReindl Harald20 Sep
                      • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPDotan Cohen20 Sep
                      • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPDotan Cohen20 Sep
                        • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPReindl Harald20 Sep
                    • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPHank20 Sep
                      • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPDotan Cohen20 Sep
        • Re: Quotes around INSERT and SELECT statements' arguments from themysql CLI and PHPDotan Cohen19 Sep