I'm looking for of creating an update string which would come out from three arrays in PHP.

E.g:

$array1 = array("field1", "field2", "field3");
$array2 = array("value1", "value2", "value3", "value4", "value5", "value6", "value7", "value8", "value9", "value10");
$prodid = array(1, 2, 3);

so that it would become something like this:

update table set $array1 = $array2 where prodid = $prodid

Is there a way to do it ?

Why have you got more values than fields? Did you mean something that would generate the following SQL for $array1/$array2 of arbitrary length (obviously they would have to be the same length..)?

update table set
(`field1`,`field2`,`field3`) = ("value1","value2","value3")
where prodid = 1;
update table set
(`field1`,`field2`,`field3`) = ("value1","value2","value3")
where prodid = 2;
update table set
(`field1`,`field2`,`field3`) = ("value1","value2","value3")
where prodid = 3

I just forgot to update the array1 while explaining. It is the same as array2.

I'm looking for this function in mySQL's website manual and I cannot see where the update statement you applied is being demonstrate ... are you sure it works this way ?

At least for mysql the only way i know of doing updates is to construct an SQL query then execute it. Some other SQL databases may be able to do it directly, but then you are into portability issues. You can always put it inside a function..


function makeSQL($fields,$values,$prodIds)
{
$fieldCount = count($fields);
if( $fieldCount < 1 || $fieldCount != count($values) )
return FALSE;
$sqlQuery = "";
foreach($prodIds as $id)
{
$sqlQuery .= "update table set ";
$sqlQuery .= "(";
for($idx=0; $idx<$fieldCount-1; $idx++)
$sqlQuery .= "`".$fields[$idx]."`,";
$sqlQuery .= "'".$fields[$idx]."') = (";
for($idx=0; $idx<$fieldCount-1; $idx++)
$sqlQuery .= "'".$values[$idx]."',";
$sqlQuery .= "'".$values[$idx]."') where prodid = '".$id."'; ";
}
return $sqlQuery;
}



$field = array("f1","f2","f3","f4");
$value = array("v1","v2","v3","v4");
$prods = array(1,2);
$sql = makeSQL($field,$value,$prods);
if( $sql !== FALSE )
echo $sql; // execute $sql
else
echo "Fail";


(I knocked this together in 10mins so there might be mistakes..)

Excellent. This looks promising. I will make the rest of the adjustments if I find any errors.

Thank you for your assistance. :)

Assembling a SQL query string like that is simple enough if all the types are the same, not so simple if you have a mix of types (think date, int, float, string). Additionally you'll want to be sure the values have been sanitized or your quoting function is up to snuff.

But you really don't want to be building your SQL statements with simple concatentation of fields and values.

To deal with this efficiently what you really want to be doing is using parameterized queries; I'm surprised that these seem to be discussed so infrequently in PHP / MySQL circles. They are the bread and butter of any serious database developer and ought to be standard equipment for *all* database application developers.

Maybe this is because it took PHP a long time to get more advanced db handling, and MySQL itself has only had prepared statements since 4.x if I recall correctly.

Read: http://ca3.php.net/manual/en/book.pdo.php (Only available since 5.1)
Specifically look at the examples in "execute": http://ca.php.net/manual/en/pdostatement.execute.php
Or you can do it all in "MySQL" sql directly: http://dev.mysql.com/doc/mysql/en/SQLPS.html

But you really don't want to be building your SQL statements with simple concatentation of fields and values.
True - I don't know if PHP has an equivalent of Java's StringBuffer. I was half-thinking in C, where i would do a bit of dodgy pointer arithmetic.. :)



To deal with this efficiently what you really want to be doing is using parameterized queries; I'm surprised that these seem to be discussed so infrequently in PHP / MySQL circles. They are the bread and butter of any serious database developer and ought to be standard equipment for *all* database application developers.

Maybe this is because it took PHP a long time to get more advanced db handling, and MySQL itself has only had prepared statements since 4.x if I recall correctly.


Suspect it is also because a lot of PHP/MySQL projects are written by people who don't specialise in database programming (i.e. don't do it day-in-day-out), and tutorials/courses don't bother mentioning them. Most programming i do is driver-level stuff, so the SQL i know can be written on the back of a stamp..

the SQL i know can be written on the back of a stamp

Then we are even, because I think I can fit the word Java on the back of a stamp and that's about the extent of my knowledge there! :)

I do think the relative newness of better SQL handling in PHP has a lot to do with the current state of affairs. But you'd think with all the PHP application attacks which come out on a regular basis, and the relative awareness people have of SQL injection attacks, that folks would be more likely to use, and talk about, tools which will help them. I'm not referring to your post above... it is just surprising how little is out there on the net on parameterized sql statement building in PHP/mysql.

For the OP, the fear you have to have with untrusted user input is that one of the "values" you get from a user will look like: ; delete from some_table; -- i.e., classic SQL injection attack:

update stocks set symbol='FOO' where symbol='RHAT';delete from stocks;
UPDATE 1
DELETE 2

A parameterized query will prevent this[1].

Here's an example of parameterized queries in Python but the principle is the same in PHP PDO, just a lot more verbose there than in Python. First an existing Postgres table:

test# select * from stocks;
date | trans | symbol | qty | price
------------+-------+--------+-----+-------
2006-01-05 | BUY | QCOM | 100 | 35
2007-02-01 | BUY | IBM | 100 | 77
2006-01-05 | BUY | IBM | 100 | 82
(3 rows)

And in Python via the DBAPI, a parameterized query is just a single line (c.execute('update stocks set symbol=%s, price=%s where symbol=%s', ('AAPL', 82, 'IBM'))) within the following example code:

$ python
Python 2.6.2 (r262:71600, Jun 14 2009, 22:42:59)
>>> import psycopg2
>>> conn = psycopg2.connect(database='test')
>>> c = conn.cursor()
>>> c.execute('select * from stocks')
>>> for row in c:
... print row
...
('2006-01-05', 'BUY', 'QCOM', 100.0, 35.0)
('2007-02-01', 'BUY', 'IBM', 100.0, 77.0)
('2006-01-05', 'BUY', 'IBM', 100.0, 82.0)
>>> # lets change things; don't let the %S fool you, these are parameter
>>> # replacement markers in this context
>>> c.execute('update stocks set symbol=%s, price=%s where symbol=%s', ('AAPL', 82, 'IBM'))
>>> conn.commit()
>>> # confirm the changes
>>> c.execute('select * from stocks')
>>> for row in c:
... print row
...
('2006-01-05', 'BUY', 'QCOM', 100.0, 35.0)
('2007-02-01', 'BUY', 'AAPL', 100.0, 82.0)
('2006-01-05', 'BUY', 'AAPL', 100.0, 82.0)
>>>

[1] Lets try the sql injection attack again, against a parameterized query:

>>> c.execute('update stocks set symbol=%s, price=%s where symbol=%s', ('AAPL', 82, 'IBM; delete from stocks;'))
>>> conn.commit()
>>> c.execute('select * from stocks')
>>> for row in c:
... print row
...
('2006-01-05', 'BUY', 'QCOM', 100.0, 35.0)
('2007-02-01', 'BUY', 'AAPL', 100.0, 82.0)
('2006-01-05', 'BUY', 'AAPL', 100.0, 82.0)

No joy, for the hacker.

Now I wonder if we can tie parameterized queries with an easy statement building method, to satisfy the OP's query. I wonder if PHP can do something like the following:

# python code
# lets load up a simple list (a one dimensional array in PHP speak)
# one each for fields and values
>>> fields
['date', 'trans', 'symbol', 'price']
>>> values
['2009-06-17', 'SELL', 'AAPL', 51]

# we want to end up with a parameterized SQL statement that
# looks like:
# c.execute('update stocks set date=%s, trans=%s, symbol=%s, price=%s ...

# here's how we build the "set" part of the statement
>>> [x + '=%s' for x in fields]
['date=%s', 'trans=%s', 'symbol=%s', 'price=%s']

# Turn the above into a one liner that produces a SQL fragment
# suitable for use in parameterized queries

>>> ', '.join([x + '=%s' for x in fields])
'date=%s, trans=%s, symbol=%s, price=%s'

# put it all together

>>> stmt = 'update stocks set ' + \
... ', '.join([x + '=%s' for x in fields])
>>> stmt
'update stocks set date=%s, trans=%s, symbol=%s, price=%s'

# using it
>>> c.execute(stmt, values)
>>> conn.commit()
>>> c.execute('select * from stocks')
>>> for row in c:
... print row
...
('2009-06-17', 'SELL', 'AAPL', 100.0, 51.0)
('2009-06-17', 'SELL', 'AAPL', 100.0, 51.0)
('2009-06-17', 'SELL', 'AAPL', 100.0, 51.0)

Whether in Python or PHP, it is probably wiser if not safer to use named parameters to avoid problems with order changes in "values" or "fields".

Sanitizing is not a problem. I'm using an extension for binding the variables before applying all queries. Thanks again for your help.

I do think the relative newness of better SQL handling in PHP has a lot to do with the current state of affairs. But you'd think with all the PHP application attacks which come out on a regular basis, and the relative awareness people have of SQL injection attacks, that folks would be more likely to use, and talk about, tools which will help them.


I think people only start to really appreciate security of scripts once they've gone through the pain of mopping up a server breakin or two. phpBB circa 2005 springs to mind..

Pretty sure that between PHP4 and PHP5 automatic quoting of user input was changed from default on to default off.