473,380 Members | 1,279 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,380 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 9489
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: snacktime | last post by:
I'm used to using the perl DBI and not very familiar with the python DB-API. I am using PyGreSQL. My question is what is the standard way to quote strings in sql queries? I didn't see any...
0
by: Andy Jackman | last post by:
Hi, I'm new to mySql. I've got a table with an integer field defined like this: l_start_wait int(9) not null I inserted a value into the table using mysql command line client with: Insert into...
0
by: M.C. Scheffers | last post by:
Hello all, I'm developing an VB application which uses the SAX parser and ADO VB to insert.update data in in MySQL. The perormance is very bad. If I use the same data and insert/update to a MS...
4
by: Robert Blackwell | last post by:
A while ago I had some help from here to make a batch file that would dump my db. Everything was working just fine until 2 days ago and I just found out. I checked my backup folder and found...
4
by: FangQ | last post by:
hi I am very new to mysql. I have a question about using the "on duplicate update" clause with insert command. my table "data" has two columns, field1 and field2, where field1 is the index...
1
by: Mitch | last post by:
I'm moving a script from MySQL to MS Access (yuck). Anyway, when I do an insert or select with an email address as a field value in odbc_exec, ODBC barfs on me. Is there some special quoting...
7
by: sanjeevcis | last post by:
Hi, Below code is regarding to insert an array values into mysql db using php. i dont know why the code is not inserting array values into database properly. If is there any thing wrong with the...
2
by: Rangasql | last post by:
I am trying to insert integer values to mysql database using the integer variable declared in C++ language, but it is not inserting . it is inserting zeroes , but not the values in the variable. Can...
2
dlite922
by: dlite922 | last post by:
I have a permission table that gives a userID permission to a module and the any actions within that module. What I want to do is duplicate his permissions to another user. Permission table...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

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.