Quotes around INSERT and SELECT statements" arguments from the mysqlCLI and PHP

I am somewhat confused as to the proper way to place quotes around
arguments in INSERT and SELECT statements. I also don't see where this
is made explicit in the fine manual.

If the column is type int, is it preferable to use single, double, or
no quotes on INSERT from the mysql cli?
If the column is type int, is it preferable to use single, double, or
no quotes on SELECT from the mysql cli?
If the column is type int, is it preferable to use single, double, or
no quotes on INSERT from PHP?
If the column is type int, is it preferable to use single, double, or
no quotes on SELECT from PHP?
Is it the same for decimal and float?

If the column is type varchar, is it preferable to use single or
double quotes on INSERT from the mysql cli?
If the column is type varchar, is it preferable to use single or
double quotes on SELECT from the mysql cli?
If the column is type varchar, is it preferable to use single or
double quotes on INSERT from PHP?
If the column is type varchar, is it preferable to use single or
double quotes on SELECT from PHP?
Is it the same for text and blob?
Also, in PHP often I see code examples with the variable wrapped in
curly brackets, inside single quotes. What is the purpose of the curly
brackets? Here is such an example:
$query="INSERT INTO names (name) VALUE ('{$userName}')";

If the column is type datetime, is it preferable to use single or
double quotes on INSERT from the mysql cli?
If the column is type datetime, is it preferable to use single or
double quotes on SELECT from the mysql cli?
If the column is type datetime, is it preferable to use single or
double quotes on INSERT from PHP?
If the column is type datetime, is it preferable to use single or
double quotes on SELECT from PHP?
What if I am using the NOW() function?

If the column is type set, is it preferable to use single or double
quotes on INSERT from the mysql cli?
If the column is type set, is it preferable to use single or double
quotes on SELECT from the mysql cli?
If the column is type set, is it preferable to use single or double
quotes on INSERT from PHP?
If the column is type set, is it preferable to use single or double
quotes on SELECT from PHP?

Thanks.

--
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

Personally I don't use any quotes for the numeric types, and single quotes for everything else. Ie:

UPDATE mytable SET int_field = 5 WHERE id = 3;
SELECT id FROM mytable WHERE int_field = 5;
UPDATE mytable SET varchar_field = 'Test' WHERE id = 3;
SELECT id FROM mytable WHERE varchar_field = 'Test';
UPDATE mytable SET datetime_field = '2011-09-18 00:00:00' WHERE id = 3;

If you are using PHP you may need to escape the single quotes if your php string is in single quotes:
$query = 'UPDATE mytable SET varchar_field = \'Test\' WHERE id = 3'

But if you are doing interpolation and your string is in double quotes, you should not need to escape:
$query = "UPDATE $table_name SET varchar_field = 'Test' WHERE id = 3"

Some people prefer to use back quotes on field names such as:
$query = "UPDATE `mytable` SET `varchar_field` = 'Test' WHERE `id` = 3"

And some people prefer to put numeric fields in quotes as well, although it is not necessary:
UPDATE mytable SET int_field = '5' WHERE id = '3';

On 9/18/11 5:00 AM, Dotan Cohen wrote:
> I am somewhat confused as to the proper way to place quotes around
> arguments in INSERT and SELECT statements. I also don't see where this
> is made explicit in the fine manual.
>
> If the column is type int, is it preferable to use single, double, or
> no quotes on INSERT from the mysql cli?
> If the column is type int, is it preferable to use single, double, or
> no quotes on SELECT from the mysql cli?
> If the column is type int, is it preferable to use single, double, or
> no quotes on INSERT from PHP?
> If the column is type int, is it preferable to use single, double, or
> no quotes on SELECT from PHP?
> Is it the same for decimal and float?
>
> If the column is type varchar, is it preferable to use single or
> double quotes on INSERT from the mysql cli?
> If the column is type varchar, is it preferable to use single or
> double quotes on SELECT from the mysql cli?
> If the column is type varchar, is it preferable to use single or
> double quotes on INSERT from PHP?
> If the column is type varchar, is it preferable to use single or
> double quotes on SELECT from PHP?
> Is it the same for text and blob?
> Also, in PHP often I see code examples with the variable wrapped in
> curly brackets, inside single quotes. What is the purpose of the curly
> brackets? Here is such an example:
> $query="INSERT INTO names (name) VALUE ('{$userName}')";
>
> If the column is type datetime, is it preferable to use single or
> double quotes on INSERT from the mysql cli?
> If the column is type datetime, is it preferable to use single or
> double quotes on SELECT from the mysql cli?
> If the column is type datetime, is it preferable to use single or
> double quotes on INSERT from PHP?
> If the column is type datetime, is it preferable to use single or
> double quotes on SELECT from PHP?
> What if I am using the NOW() function?
>
> If the column is type set, is it preferable to use single or double
> quotes on INSERT from the mysql cli?
> If the column is type set, is it preferable to use single or double
> quotes on SELECT from the mysql cli?
> If the column is type set, is it preferable to use single or double
> quotes on INSERT from PHP?
> If the column is type set, is it preferable to use single or double
> quotes on SELECT from PHP?
>
> Thanks.
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

On Sun, Sep 18, 2011 at 17:44, Brandon Phelps wrote:
> Personally I don't use any quotes for the numeric types, and single quote=
s
> for everything else.  Ie:
>

Thanks, Brandon. I understand then that quote type is a matter of
taste. I always use double quotes in PHP and I've only recently
started putting ticks around table and column names. I'll stick to
your convention of no quotes around numerics and single quotes around
everything else.

Have a terrific week!

--=20
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

--0016367fb4bd0a768404ad40dd83
Content-Type: text/plain; charset=ISO-8859-1

On Sun, Sep 18, 2011 at 12:28 PM, Dotan Cohen wrote:

> On Sun, Sep 18, 2011 at 17:44, Brandon Phelps wrote:
> > Personally I don't use any quotes for the numeric types, and single
> quotes
> > for everything else. Ie:
> >
>
> Thanks, Brandon. I understand then that quote type is a matter of
> taste. I always use double quotes in PHP and I've only recently
> started putting ticks around table and column names. I'll stick to
> your convention of no quotes around numerics and single quotes around
> everything else.
>
>
I agree with Brandon's suggestions, I would just add when using numeric
types in PHP statements where you have a variable replacement, for instance:

$sql="INSERT into table VALUES ('$id','$val')";

where $id is a numeric variable in PHP and a numeric field in the table,
I'll include the $id in single quotes in the PHP statement, so even if the
value of $id is null, alpha, or invalid (not numeric) it does not generate a
mysql syntax error. Otherwise, without the single quotes, the statement
would be:

INSERT into table VALUES (,'');

which would cause a syntax error. If you include the single quotes, it
becomes:

INSERT into table VALUES ('','')

which won't cause a syntax error, but might cause some logic errors in the
database. The choice is yours.

--0016367fb4bd0a768404ad40dd83--

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

--------------enig220C5B58AA309ADD97F505AD
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable



Am 19.09.2011 03:00, schrieb Hank:
> I agree with Brandon's suggestions, I would just add when using numeric=

> types in PHP statements where you have a variable replacement, for inst=
ance:
>=20
> $sql=3D"INSERT into table VALUES ('$id','$val')";
>=20
> where $id is a numeric variable in PHP and a numeric field in the table=
,
> I'll include the $id in single quotes in the PHP statement, so even if =
the
> value of $id is null, alpha, or invalid (not numeric) it does not gener=
ate a
> mysql syntax error

what ugly style - if it is not numeric and you throw it to the database
you are one of the many with a sql-injection because if you are get
ivalid values until there you have done no sanitize before and do not her=
e

$sql=3D"INSERT into table VALUES (" . (int)$id . ",'" . mysql_real_escape=
_string($val) . "')";
or using a abstraction-layer (simple self written class)
$sql=3D"INSERT into table VALUES (" . (int)$id . ",'" . $db->escape_strin=
g($val) . "')";

all other things in the context of hand-written queries are all the nice =
one we read every
day in the news and should NOT recommended because the next beginner read=
ing this makes all
the mistakes again



--------------enig220C5B58AA309ADD97F505AD
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/

iEYEARECAAYFAk52yV8ACgkQhmBjz394AnkPMQCfQ4nvemoWSouQsKInq/bT 7Fh4
1IIAoJM8CNs/PBhwAep+MOJJUy4458xy
=qhxJ
-----END PGP SIGNATURE-----

--------------enig220C5B58AA309ADD97F505AD--

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

On Mon, Sep 19, 2011 at 04:00, Hank wrote:
> I agree with Brandon's suggestions, I would just add when using numeric
> types in PHP statements where you have a variable replacement, for instan=
ce:
>
> $sql=3D"INSERT into table VALUES ('$id','$val')";
>
> where $id is a numeric variable in PHP and a numeric field in the table,
> I'll include the $id in single quotes in the PHP statement, so even if th=
e
> value of $id is null, alpha, or invalid (not numeric) it does not generat=
e a
> mysql syntax error. Otherwise, without the single quotes, the statement
> would be:
>
> INSERT into table VALUES (,'');
>
>  which would cause a syntax error.  If you include the single q=
uotes, it
> becomes:
>
> INSERT into table VALUES ('','')
>
> which won't cause a syntax error, but might cause some logic errors in th=
e
> database.  The choice is yours.
>

â€=8EThanks, that is a good point. I would actually prefer errors to ar=
ise
on insert then a potentially inconsistent database or bad data. I
should definitely learn to use stored procedures, I know.

That said, I do go to great lengths to validate my data. What is an
"alpha" value? I do check is_numeric() and null, of course.

--=20
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

On Mon, Sep 19, 2011 at 07:47, Reindl Harald wrote:
> what ugly style - if it is not numeric and you throw it to the database
> you are one of the many with a sql-injection because if you are get
> ivalid values until there you have done no sanitize before and do not here
>
> $sql="INSERT into table VALUES (" . (int)$id . ",'" . mysql_real_escape_string($val) . "')";
> or using a abstraction-layer (simple self written class)
> $sql="INSERT into table VALUES (" . (int)$id . ",'" . $db->escape_string($val) . "')";
>
> all other things in the context of hand-written queries are all the nice one we read every
> day in the news and should NOT recommended because the next beginner reading this makes all
> the mistakes again
>

Thanks, Reindi. I actually do something like this (simplified, in real
code I use an array and a small custom function):
$mysqlName=mysql_real_escape_string($name);
Then, in the query I can see that all my variables start with $mysql*
so I know that they have been sanitized.

--
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

--0016364d1d6f02feef04ad4c8ad3
Content-Type: text/plain; charset=ISO-8859-1

>
> what ugly style - if it is not numeric and you throw it to the database
> you are one of the many with a sql-injection because if you are get
> ivalid values until there you have done no sanitize before and do not here
>
>
It's a matter of opinion. I never said the data wasn't sanitized (it is).
But sometimes calculated values or bugs in PHP code end up with a null
variable field. I was just suggesting the choice between two errors -- one
syntax which will generate a hard failure of the query and likely whatever
page, or a soft logical error, which won't. In either case, I have error
trapping to catch both types of errors and alert me to them. I prefer the
errors to be logical ones and not syntax errors.


> $sql="INSERT into table VALUES (" . (int)$id . ",'" .
mysql_real_escape_string($val) . "')";
> or using a abstraction-layer (simple self written class)
> $sql="INSERT into table VALUES (" . (int)$id . ",'" .
$db->escape_string($val) . "')";

I think what you posted is ugly "style" which makes reading the actual SQL
in PHP code much harder to read and debug. The data validation should take
place elsewhere long before it gets to constructing the SQL statement.

--0016364d1d6f02feef04ad4c8ad3--

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

--------------enig09D1C42EEE10E545A92F7FCD
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable



Am 19.09.2011 16:55, schrieb Hank:
>>
>> what ugly style - if it is not numeric and you throw it to the databas=
e
>> you are one of the many with a sql-injection because if you are get
>> ivalid values until there you have done no sanitize before and do not =
here
>>
>>
> It's a matter of opinion. I never said the data wasn't sanitized (it i=
s).
> But sometimes calculated values or bugs in PHP code end up with a null=

> variable field. I was just suggesting the choice between two errors --=
one
> syntax which will generate a hard failure of the query and likely whate=
ver
> page, or a soft logical error, which won't. In either case, I have erro=
r
> trapping to catch both types of errors and alert me to them. I prefer t=
he
> errors to be logical ones and not syntax errors.
>=20
>=20
>> $sql=3D"INSERT into table VALUES (" . (int)$id . ",'" .
> mysql_real_escape_string($val) . "')";
>> or using a abstraction-layer (simple self written class)
>> $sql=3D"INSERT into table VALUES (" . (int)$id . ",'" .
> $db->escape_string($val) . "')";
>=20
> I think what you posted is ugly "style" which makes reading the actual =
SQL
> in PHP code much harder to read and debug. The data validation should =
take
> place elsewhere long before it gets to constructing the SQL statement.

it is not because it is clear that it is sanitized instead hope and pray
thousands of layers somewhere else did it - for a inline-query the best
solution, if you are using a framework you will never have the "insert in=
to"
at this place!

what i meant as ugly is that you are somewhere writing an inline-query an=
d
are not sure if it is a number or not - so it is NOT sanitized before
because if you tell me it is you sanitze does not work if you get a non-i=
nteger
at this point and you sanitze-method has to throw the error long before
if it is really working


--------------enig09D1C42EEE10E545A92F7FCD
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/

iEYEARECAAYFAk53W6cACgkQhmBjz394AnkZBwCcCX2iNthsBgrjHZaZMChO wHJ9
v6cAoIvSgP/xFJZr2Q8Xw3bgwse+Lld+
=efaE
-----END PGP SIGNATURE-----

--------------enig09D1C42EEE10E545A92F7FCD--

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

On Mon, Sep 19, 2011 at 18:11, Reindl Harald wrote:
> it is not because it is clear that it is sanitized instead hope and pray
> thousands of layers somewhere else did it - for a inline-query the best
> solution, if you are using a framework you will never have the "insert into"
> at this place!
>
> what i meant as ugly is that you are somewhere writing an inline-query and
> are not sure if it is a number or not - so it is NOT sanitized before
> because if you tell me it is you sanitze does not work if you get a non-integer
> at this point and you sanitze-method has to throw the error long before
> if it is really working
>


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]}')";

The resulting SQL query is easy to read, and I know that everything is
escaped. No operations are ever to be performed on $M. I need to look
into a way of making it immutable (add and read only). I could do it
with an object but I prefer an array. Actually, an array wrapped in an
object could perform the escaping itself, making me doubly sure that
some other dev didn't forget to escape while playing with the code.

By the way, I've never gotten a godd explanation about why to wrap the
variables in PHP MySQL queries with curly brackets. I don't even
remember where I picked up the habit. Does anybody here know?


--
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

--0016364d25f38bfd9b04ad529e7b
Content-Type: text/plain; charset=ISO-8859-1

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?

--0016364d25f38bfd9b04ad529e7b--

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

On Tue, Sep 20, 2011 at 01:11, Hank 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 that
>> 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 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=3Dmysql_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.


--=20
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

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

iEYEARECAAYFAk53xsgACgkQhmBjz394AnmfzACfZrh93Rpd59Q9qprS8WqB FlML
HPMAoI1zJWxRaMH1EjJZmiSrtf3AoqxH
=7KeV
-----END PGP SIGNATURE-----

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

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

--001485f4239a3b103904ad536f49
Content-Type: text/plain; charset=ISO-8859-1

>
>
> 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);
>
>
But you're doing exactly that right before the query anyway with:

$M[username]=mysql_real_escape_string($username);

You're just complicating things with the addition of an unneeded array. It
seems much simpler and less cluttered to just do:
$someVar=mysql_real_escape_string($someVar);
before your insert. All you are doing is changing "$someVar" to "$M[...]"
and then using $M[...] in the query. I really don't see the difference or
benefit of using your array here. Both methods are doing exactly the same
thing, except one is more convoluted.

Now on the other hand, if you have several elements in the array $M to be
inserted, and have a function like this to escape them all at once:

for each ($M as &$val) $val= mysql_real_escape_string($val);

then your method starts to make more sense.

-Hank

--001485f4239a3b103904ad536f49--

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

On Tue, Sep 20, 2011 at 02:09, Hank wrote:
>>
>> 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);
>>
>
> But you're doing exactly that right before the query anyway with:
> $M[username]=3Dmysql_real_escape_string($username);
> You're just complicating things with the addition of an unneeded array. =
 It
> seems much simpler and less cluttered to just do:
>           $someVar=3Dmysql_real_escape_string($s=
omeVar);
> before your insert.  All you are doing is changing "$someVar" to "$M=
[...]"
> and then using $M[...] in the query.  I really don't see the differe=
nce or
> benefit of using your array here.  Both methods are doing exactly th=
e same
> thing, except one is more convoluted.

I know that this has been escaped:
$query=3D"INSERT INTO table (username) VALUES ('{$M[username]}')";

This, I don't know if it has been escaped or not:
$query=3D"INSERT INTO table (username) VALUES ('{$username}')";


> Now on the other hand, if you have several elements in the array $M to be
> inserted, and have a function like this to escape them all at once:
> for each ($M as &$val)  $val=Â=A0mysql_real_escape_string($val);
> then your method starts to make more sense.

I could foreach it. Or not. It doesn't matter. The point is having
known-safe variables being used in the query, which are also easy to
read.

--=20
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

On Tue, Sep 20, 2011 at 01:48, Reindl Harald wrote=
:
> 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
> }
>
>

You are right, using a class has many benefits. I might do that on a
future project. Thanks.

--=20
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

On Tue, Sep 20, 2011 at 01:48, Reindl Harald wrote:
> 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
>

By the way, the database connection is include()ed from a file outside
the webroot. This way if Apache is ever compromised or for whatever
reason stops parsing the PHP, the resulting code returned to the
browser won't have the daabase info (especially the password).

--
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Quotes around INSERT and SELECT statements" arguments from themysql CLI and PHP

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



Am 20.09.2011 01:23, schrieb Dotan Cohen:
> On Tue, Sep 20, 2011 at 01:48, Reindl Harald w=
rote:
>> 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 tab=
le
>> and use intval(), doubleval() or mysql_real_escape-String
>>
> By the way, the database connection is include()ed from a file outside
> the webroot. This way if Apache is ever compromised or for whatever
> reason stops parsing the PHP, the resulting code returned to the
> browser won't have the daabase info (especially the password)

if stops parsing - yes, but not relevant if it is in a include
if the machine is compromised it does not matter
someone could read your files can read also the include outside the docro=
ot


--------------enig3BA8B3D186B992D6A7F14918
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/

iEYEARECAAYFAk53z9gACgkQhmBjz394AnlkhACghVfxzXrKJTiFCS6JPT6j IVY0
dJsAn11psDkun9OAPjUfqK4bBYu1ZkTp
=n9r3
-----END PGP SIGNATURE-----

--------------enig3BA8B3D186B992D6A7F14918--