From: Reindl Harald
Date: September 19 2011 10:48pm
Subject: Re: Quotes around INSERT and SELECT statements' arguments from the
 mysql CLI and PHP
List-Archive: http://lists.mysql.com/mysql/225775
Message-Id: <4E77C6C8.8090405@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="------------enigEE9D6BA5A02B40C91CDF57E0"

--------------enigEE9D6BA5A02B40C91CDF57E0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable



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=3D$_POST['username'];
>>> // do some stuff with username here
>>> $M=3Darray();  // Array of things to be inserted into MySQL
>>> $M[username]=3Dmysql_real_escape_string($username); // Everything tha=
t
>>> goes into $M is escaped
>>> $query=3D"INSERT INTO table (username) VALUES ('{$M[username]}')";
>>>
>>>
>> I'm not sure I'm seeing why, in particular, you are using an array her=
e?
>>
>=20
> 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=3Dmysql_real_escape_string($someVar);
>=20
> 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-me=
thods
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 desig=
end you
can throw the whole $_POST to it without thinikng about datatypes and ign=
ore
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
}


--------------enigEE9D6BA5A02B40C91CDF57E0
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk53xsgACgkQhmBjz394AnmfzACfZrh93Rpd59Q9qprS8WqBFlML
HPMAoI1zJWxRaMH1EjJZmiSrtf3AoqxH
=7KeV
-----END PGP SIGNATURE-----

--------------enigEE9D6BA5A02B40C91CDF57E0--