Quoting integer values on MySQL INSERT and UPDATE

[Chuck Anderson]

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("SH OW COLUMNS FROM `table_1`", $link);

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

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

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

}

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

mysql_query($qu ery, $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.
*************** *************** *****

[Jerry Stuckle]

Re: Quoting integer values on MySQL INSERT and UPDATE

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("SH OW COLUMNS FROM `table_1`", $link);
>
$query = 'INSERT INTO `table_1` SET';
>
while ($cols_row=mysq l_fetch_array($ cols_result))
{
if ($cols_row['Field'] == 'table_1_id' // the auto_increment primary key
continue;
>
$query .= "\n
{$cols_row['Field']}='{$safe_value s[$cols_row['Field']]}',";
>
}
>
$query = substr($query, 0, -1); // remove last comma
>
mysql_query($qu ery, $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.
jstucklex@attgl obal.net
=============== ===

[Tim Roberts]

Re: Quoting integer values on MySQL INSERT and UPDATE

Chuck Anderson <websiteaddress @seemy.sigwrote :

>
>...
>-----------------------------------------
>// First "clean" and Put all POST vars into array $safe_values
>// Then ...
>
>$cols_result = mysql_query("SH OW COLUMNS FROM `table_1`", $link);
>
>$query = 'INSERT INTO `table_1` SET';
>
>while ($cols_row=mysq l_fetch_array($ cols_result))
>{
if ($cols_row['Field'] == 'table_1_id' // the auto_increment primary key
continue;
>
$query .= "\n
>{$cols_row['Field']}='{$safe_value s[$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, timr@probo.com
Providenza & Boekelheide, Inc.

[Chuck Anderson]

Re: Quoting integer values on MySQL INSERT and UPDATE

Tim Roberts wrote:

Chuck Anderson <websiteaddress @seemy.sigwrote :
>

>...
>-----------------------------------------
>// First "clean" and Put all POST vars into array $safe_values
>// Then ...
>>
>$cols_result = mysql_query("SH OW COLUMNS FROM `table_1`", $link);
>>
>$query = 'INSERT INTO `table_1` SET';
>>
>while ($cols_row=mysq l_fetch_array($ cols_result))
>{
> if ($cols_row['Field'] == 'table_1_id' // the auto_increment primary key
> continue;
>>
> $query .= "\n
>{$cols_row['Field']}='{$safe_value s[$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.
*************** *************** *****

[=?UTF-8?B?SXbDoW4gU8OhbmNoZXogT3J0ZWdh?=]

Re: Quoting integer values on MySQL INSERT and UPDATE

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

[Chuck Anderson]

Re: Quoting integer values on MySQL INSERT and UPDATE

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.
*************** *************** *****

[Tim Roberts]

Re: Quoting integer values on MySQL INSERT and UPDATE

Chuck Anderson <websiteaddress @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, timr@probo.com
Providenza & Boekelheide, Inc.