MySQL INSERT quoting

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
nandesu
Forum Newbie
Posts: 1
Joined: Fri Jul 11, 2003 3:32 pm
Location: United States
Contact:

MySQL INSERT quoting

Post by nandesu »

I'm having issues with uprgrading from PHP-4-2.1 to PHP-4.2.2.

For ever I've been using the following syntax:
(Assumptions, $a is an array with index $a[0] containing some data.
db_query is a wrapper function for mysql_query() )

Code: Select all

$qid = db_query(" INSERT INTO table (example) VALUES ('$aї0]') ");
This now gives me the following error:
Parse error: parse error, expecting `']''
Now with PHP-4.2.2 the only syntax that will work is:

Code: Select all

$qid = db_query(" INSERT INTO table (example) VALUES ('".$aї"0"]."') ");
Why is the inital style now invalid? Why change it in a minor revision and is there per chance an option to enable to the old style via php.ini?

I'm really not wanting to sort through over 300,000+ lines of code for every INSERT and UPDATE query ..

Where can one find a resource that really lists the "proper PHP by the letter" way of coding things? According to four differnt printed books I have on PHP the first version is a proper syntax. I search on pear.php.net last night and they mention nothing in this regard because they use their own VALUES(?,?,?)

Any information is greatly apprecaited, and is this tendency to release security updates that will wreak havoc upon your entire project code base a common issue with PHP?

Thanks in advance!
User avatar
Slippy
Forum Contributor
Posts: 113
Joined: Sat Jul 12, 2003 11:31 pm
Location: Vancouver eh!

Post by Slippy »

I can't tell you why it is happening. All I can suggest is that you will be safer if you are more explicit with your code.

I always use the syntax that you used in the second example, for all strings and I have yet to run into a problem with PHP handling it.

That is to say that for even a simple string I will use the dots and quotes method even though PHP supports simpler methods.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Does this work?

Code: Select all

$sql = "INSERT INTO table (example) VALUES ('{$a[0]}') ";
$qid = db_query($sql);
I am surprised that your syntax is having problems - have you thought about upgrading to PHP 4.3 to see if it still occurs.

Mac
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post by Heavy »

Please check out strings in the PHP manual. Take greater note to:
"Complex (curly) syntax"

example 1:
echo "blabla $a[0]";
...could really be misunderstood, maybe not by the script engine as of PHP-4-2.1, but I don't think it is perfectly clear what it means.

But:
example 2:
echo "blabla {$a[0]}";
...can not be interpreted wrong, and it works.

Maybe that is the reason why the first example does not work anymore. Zend maybe decided to clear things up.


But I do agree with you. It is awfully often important things change in PHP, so that entire applications need some hours of boring editing to make it work after an upgrade. This may due to the fact that PHP has changed direction. It started out as a simple script capability system to make things like <?php echo date('Y-m-d',time()); ?> spice up your pages. Nowadays it is capable of doing almost all that a "normal" web programmer wants. When development changes direction, these things happen. We should of course not forget that many (maybe most) changes between releases are due to security issues that have been fixed and require a new way of coding with PHP. But hey! It's still free! And it is REALLY good!
User avatar
Heavy
Forum Contributor
Posts: 478
Joined: Sun Sep 22, 2002 7:36 am
Location: Viksjöfors, Hälsingland, Sweden
Contact:

Post by Heavy »

To make vars for usage in sql strings be secure and portable, you might want to do this with your $_POST array:

Code: Select all

<?php
$MagicQuotes = get_magic_quotes_gpc ();
if (!$MagicQuotes){
	foreach($_POST as $key => $_posted){
		if (is_array($_posted)){
			foreach($_posted as $key1 => $_posted1){
				$_posted[$key1] = addslashes($_posted1);
			}
		}else{
			$_POST[$key] = addslashes($_posted);
		}
	}
}

if (!$MagicQuotes){
	foreach($_GET as $key => $_gotten){
		$_GET[$key] = addslashes($_gotten);
	}
}

?>
It's a bit ugly, but it's old code as well. :?
Run that before you use any POSTed or GETted vars in sql strings with single quotation in value lists or whatever.

Then you can move it along different hosts and avoid SQL-injection.
Post Reply