By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,521 Members | 1,460 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,521 IT Pros & Developers. It's quick & easy.

Quoting integer values on MySQL INSERT and UPDATE

P: n/a
Granted, this is mostly a mysql question, but I think understanding the
Php makes it easy to understand why my question is necessary.

I'm writing a Php loop to create INSERT and UPDATE queries from $_POST
data. All posted variables have to use the same name as the respective
column in the database for this to work. I also have know what the
primary - auto increment column is so I do not try to insert or update it.

-----------------------------------------
// First "clean" and Put all POST vars into array $safe_values
// Then ...

$cols_result = mysql_query("SHOW COLUMNS FROM `table_1`", $link);

$query = 'INSERT INTO `table_1` SET';

while ($cols_row=mysql_fetch_array($cols_result))
{
if ($cols_row['Field'] == 'table_1_id' // the auto_increment primary key
continue;

$query .= "\n
{$cols_row['Field']}='{$safe_values[$cols_row['Field']]}',";

}

$query = substr($query, 0, -1); // remove last comma

mysql_query($query, $link);
------------------------------------
(The \n's is merely to "beautify" the query if I want to echo it within
<pretags or using nl2br.)

I need to quote every value I insert, even if it is an integer, in case
it does not exist. What I want to know is if there is any problem with
quoting the integer values as well as the strings. My guess is no
(INSERTS and UPDATES work). I just want to be sure I'm not creating
unknown or unforeseen problems for myself.

--
*****************************
Chuck Anderson • Boulder, CO
http://www.CycleTourist.com
Nothing he's got he really needs
Twenty first century schizoid man.
***********************************

Sep 25 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Chuck Anderson wrote:
Granted, this is mostly a mysql question, but I think understanding the
Php makes it easy to understand why my question is necessary.

I'm writing a Php loop to create INSERT and UPDATE queries from $_POST
data. All posted variables have to use the same name as the respective
column in the database for this to work. I also have know what the
primary - auto increment column is so I do not try to insert or update it.

-----------------------------------------
// First "clean" and Put all POST vars into array $safe_values
// Then ...

$cols_result = mysql_query("SHOW COLUMNS FROM `table_1`", $link);

$query = 'INSERT INTO `table_1` SET';

while ($cols_row=mysql_fetch_array($cols_result))
{
if ($cols_row['Field'] == 'table_1_id' // the auto_increment primary key
continue;

$query .= "\n
{$cols_row['Field']}='{$safe_values[$cols_row['Field']]}',";

}

$query = substr($query, 0, -1); // remove last comma

mysql_query($query, $link);
------------------------------------
(The \n's is merely to "beautify" the query if I want to echo it within
<pretags or using nl2br.)

I need to quote every value I insert, even if it is an integer, in case
it does not exist. What I want to know is if there is any problem with
quoting the integer values as well as the strings. My guess is no
(INSERTS and UPDATES work). I just want to be sure I'm not creating
unknown or unforeseen problems for myself.
You should be asking this in comp.databases.mysql. All the SQL
statement is to PHP is a string; MySQL is the interpreter of the SQL
statements.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

Sep 25 '08 #2

P: n/a
Chuck Anderson <we************@seemy.sigwrote:
>
...
-----------------------------------------
// First "clean" and Put all POST vars into array $safe_values
// Then ...

$cols_result = mysql_query("SHOW COLUMNS FROM `table_1`", $link);

$query = 'INSERT INTO `table_1` SET';

while ($cols_row=mysql_fetch_array($cols_result))
{
if ($cols_row['Field'] == 'table_1_id' // the auto_increment primary key
continue;

$query .= "\n
{$cols_row['Field']}='{$safe_values[$cols_row['Field']]}',";

}
Do you realize that the "INSERT INTO ... SET x=y" syntax is a MySQL
extension? It's not standard SQL. If you ever want to use a different
database, you'll have to remember that.
--
Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.
Sep 25 '08 #3

P: n/a
Tim Roberts wrote:
Chuck Anderson <we************@seemy.sigwrote:
>...
-----------------------------------------
// First "clean" and Put all POST vars into array $safe_values
// Then ...

$cols_result = mysql_query("SHOW COLUMNS FROM `table_1`", $link);

$query = 'INSERT INTO `table_1` SET';

while ($cols_row=mysql_fetch_array($cols_result))
{
if ($cols_row['Field'] == 'table_1_id' // the auto_increment primary key
continue;

$query .= "\n
{$cols_row['Field']}='{$safe_values[$cols_row['Field']]}',";

}

Do you realize that the "INSERT INTO ... SET x=y" syntax is a MySQL
extension? It's not standard SQL. If you ever want to use a different
database, you'll have to remember that.
So the more generalized method would be:

INSERT INTO TABLE (field,1, field2, field3, ....)
(value1, value2, value3, .....)

?

--
*****************************
Chuck Anderson • Boulder, CO
http://www.CycleTourist.com
Nothing he's got he really needs
Twenty first century schizoid man.
***********************************

Sep 25 '08 #4

P: n/a
Chuck Anderson wrote:

[...]
I need to quote every value I insert, even if it is an integer, in case
it does not exist. What I want to know is if there is any problem with
quoting the integer values as well as the strings.
My suggestion is to stop using SQL strings with quotes and escaped text, and
start using PDO and bound variables. You won't have to escape anything,
thus eliminating any problems related to quoting and SQL injections.

--
----------------------------------
Iván Sánchez Ortega -ivan-algarroba-sanchezortega-punto-es-

Proudly running Debian Linux with 2.6.26-1-amd64 kernel, KDE 3.5.9, and PHP
5.2.6-3 generating this signature.
Uptime: 00:28:45 up 35 days, 12:24, 2 users, load average: 0.53, 0.34,
0.27

Sep 25 '08 #5

P: n/a
Iván Sánchez Ortega wrote:
Chuck Anderson wrote:

[...]
>I need to quote every value I insert, even if it is an integer, in case
it does not exist. What I want to know is if there is any problem with
quoting the integer values as well as the strings.

My suggestion is to stop using SQL strings with quotes and escaped text, and
start using PDO and bound variables. You won't have to escape anything,
thus eliminating any problems related to quoting and SQL injections.

I agree (or something similar ..... most likely I'll use mysqli prepared
statements). I will probably start my next project that way, but I'm
too far along in this one (most of the scripts already exist) and it's
not worth the time on this project to, first learn how to use them, and
then convert and retest every thing.

--
*****************************
Chuck Anderson • Boulder, CO
http://www.CycleTourist.com
Nothing he's got he really needs
Twenty first century schizoid man.
***********************************

Sep 26 '08 #6

P: n/a
Chuck Anderson <we************@seemy.sigwrote:
>Tim Roberts wrote:
>>
Do you realize that the "INSERT INTO ... SET x=y" syntax is a MySQL
extension? It's not standard SQL. If you ever want to use a different
database, you'll have to remember that.

So the more generalized method would be:

INSERT INTO TABLE (field,1, field2, field3, ....)
(value1, value2, value3, .....)
?
That's close:
INSERT INTO TABLE (field1, field2, field3, ... )
VALUES (value1, value2, value2, ... );

As long as you stay with MySQL, it's personal preference (and I have to say
the "SET" syntax does look prettier). It's just something to remember for
later.
--
Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc.
Sep 27 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.