Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Mon, Sep 19, 2011 at 04:00, Hank hes...@gmail.com 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 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.
Thanks, that is a good point. I would actually prefer errors to arise
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.
--
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=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Mon, Sep 19, 2011 at 07:47, Reindl Harald h.rei...@thelounge.net 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=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
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.
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 19.09.2011 16:55, schrieb Hank: 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. 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 signature.asc Description: OpenPGP digital signature
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Mon, Sep 19, 2011 at 18:11, Reindl Harald h.rei...@thelounge.net 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=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
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?
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 01:11, Hank hes...@gmail.com 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.
--
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=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 20.09.2011 00:39, schrieb Dotan Cohen:
On Tue, Sep 20, 2011 at 01:11, Hank hes...@gmail.com 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
}
signature.asc
Description: OpenPGP digital signature
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
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
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 02:09, Hank hes...@gmail.com 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=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.
I know that this has been escaped:
$query=INSERT INTO table (username) VALUES ('{$M[username]}');
This, I don't know if it has been escaped or not:
$query=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= mysql_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.
--
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=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 01:48, Reindl Harald h.rei...@thelounge.net 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
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
}
You are right, using a class has many benefits. I might do that on a
future project. 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=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Tue, Sep 20, 2011 at 01:48, Reindl Harald h.rei...@thelounge.net 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=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
Am 20.09.2011 01:23, schrieb Dotan Cohen: On Tue, Sep 20, 2011 at 01:48, Reindl Harald h.rei...@thelounge.net 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) 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 docroot signature.asc Description: OpenPGP digital signature
Re: Quotes around INSERT and SELECT statements' arguments from the mysql 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=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Sun, Sep 18, 2011 at 17:44, Brandon Phelps bphe...@gls.com 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. Have a terrific week! -- 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=arch...@jab.org
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
On Sun, Sep 18, 2011 at 12:28 PM, Dotan Cohen dotanco...@gmail.com wrote:
On Sun, Sep 18, 2011 at 17:44, Brandon Phelps bphe...@gls.com 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.
Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP
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 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
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
signature.asc
Description: OpenPGP digital signature
