471,616 Members | 1,908 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,616 software developers and data experts.

Quoting integer values on MySQL INSERT and UPDATE

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
6 9337
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
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
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
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
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
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.

Similar topics

2 posts views Thread by snacktime | last post: by
reply views Thread by Andy Jackman | last post: by
reply views Thread by M.C. Scheffers | last post: by
4 posts views Thread by Robert Blackwell | last post: by
1 post views Thread by Mitch | last post: by
7 posts views Thread by sanjeevcis | last post: by
2 posts views Thread by Rangasql | last post: by
reply views Thread by leo001 | last post: by
1 post views Thread by ZEDKYRIE | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.