Posted by Michael Fesser on 09/01/07 07:56

..oO(The Natural Philosopher)

>Dunno what that means, but since Ive been working all day on this sort
>of stuff..
>
>1/. $q = "select * from table1 where id = '".$id."'";
>
>is what you want. Note the order of quotes and dots.

Only if the ID is a string. Numeric values are not quoted. And with
embedded variables or sprintf() you won't even have to worry about the
order of quotes and dots, which is a really error-prone style of writing
a query:

$q = "SELECT * FROM table1 WHERE id = $id";

$q = sprintf("SELECT * FROM table1 WHERE id = %u", $id);

>2/. On my server - virgin php5 apache2 setup, all POST data gets escaped
>with backslashes into a format that is completely digestible by MySql.

Relying on magic quotes is unreliable and won't work at all in PHP 6.
Better start fixing your code now.

>3/. One coding bug showed up interestingly when I had done
>
>$new_id=mysql_insert_id;
>
>instead of
>
>$new_id=mysql_insert_id();
>
>The insert of what surprisinlgly was NOT a syntax error

Your error_reporting is set improperly.

>4/. I did have an issue with redisplaying data that had come from a POST
>derived form..now normally I update the database, then read the data
>from the database back into the form: In this case I was testing 'failed
>to update, re-enter some data' and the backslashed stuff gave me issues
>with quotes and backslashes.

A typical magic quotes problem.

>Basically a variable that might be "Fred Bloggs" became \"Fred Bloggs\"
>after being POSTED. This went into mysql fine using the sort of query I
>outlined above. And checking using direct database access showed the
>data in *there* was in fact "Fred Bloggs"
>
>However if I wanted to reinsert that into e.g. text box as i.e.
>value="\"Fred Bloggs\"" it didn't work too well at all. Repeated updates
>left me with truncated data and/or multiple backlashes

A bug in your code. Usually all you have to do is this:

* when receiving the POST data, remove slashes if magic quotes are on
* use a proper escaping function to insert the data into the DB
* when printing it out, use htmlspecialchars()

That's it. Correct, reliable and no problem with slashes.

>Any POST data that needs to be inserted into input fields and the like -
>goes through this:-
>
>function sanitise($string)
>{
>$string=stripslashes($string); // remove any backslashes
>$string=htmlspecialchars($string); // turn oddities that HTML barfs
> // on into ampersand stuff
>return $string;
>}

If you have to call stripslashes() on output you've made a mistake
somewhere else. It's never necessary for printing out something.

>inside it, but that showed up as "Fred Bloggs" IN the box, and POSTed up
>correctly as \"Fred Bloggs\" and went into SQL as "Fred Bloggs"
>
>So the correct php to generate THAT fragment would be
>
>echo ("value=\"".htmlspecialcars($my_value)."\"");

printf("value='%s'", htmlspecialcars($my_value));

Micha

[Back to original message]