Page 1 of 2

input on form breaks database add. (it's strange)

Posted: Sun Jul 30, 2006 4:41 am
by robster
Hi there,

I've come across this kind of thing before, where say, a " or a ' from a form will break the database update. Currently this is how the code is:

Code: Select all

$history = nl2br($history);
$Update = mysql_db_query ($dbname, "UPDATE colour_history SET history=trim('$history') WHERE id='$id'");
and it works fine with ' and " etc.

The strange bug I'm having though is breaking (ie: no update) on using < or >. But it's not that simple. I'll show you what breaks and what doesn't. Strange!!!

Code: Select all

<> (doesn't break)
<hello> (breaks)
< hello > (doesn't break)
erm... I'm lost :)

Any advice GREATLY appreciated.

Rob

Posted: Sun Jul 30, 2006 4:48 am
by Charles256
because < > are respectively the less than and greater than operators.if you want to literally insert that you probably should enclose the the string in quotes and escape those operators..i think..not at a place i can test. but i'm fairly sure i'm right. run it through mysql_real_escape_string if memory serves.

Posted: Sun Jul 30, 2006 5:28 am
by robster
I had a look at mysql_real_escape_string but my php knowlege is not that great and it didn't work as i applied it.

If someone could further explain I'd really appreciate it.


rob

Posted: Sun Jul 30, 2006 5:33 am
by Charles256
erm.it didn't work? show me exactly what you're doing code wise.the input your using and an example of the code that is processing it. then i can explain more... I think you might want to consider grabbing a few beginner PHP books too.

Posted: Sun Jul 30, 2006 10:22 am
by RobertGonzalez
Read the manual on mysql_real_escape_string(). Although, according to the documentation, it will not do anything to '<' or '>'.

Run this query in your mysql utility (phpMyAdmin or the like) and tell us what you get back...

Code: Select all

SELECT VERSION();

Posted: Sun Jul 30, 2006 3:46 pm
by robster

Code: Select all

4.1.14-nt
This is my test server. I run on XPPro here, then there the application runs on both macs and pc's, both under firefox.

Rob

Posted: Sun Jul 30, 2006 3:53 pm
by RobertGonzalez
And all instances experience the same error?

Posted: Sun Jul 30, 2006 4:07 pm
by volka
Everah wrote:And all instances experience the same error?
And what error exactly occurs? mysql_error should know.

Code: Select all

$history = nl2br($history);
$query = "UPDATE colour_history SET history=trim('$history') WHERE id='$id'";
$Update = mysql_db_query ($dbname, $query) or die(mysql_error().': '.htmlentities($query));
btw:
http://de2.php.net/mysql_db_query wrote:This function is deprecated, do not use this function. Use mysql_select_db() and mysql_query() instead.

Posted: Sun Jul 30, 2006 5:03 pm
by RobertGonzalez
Run this query from phpMyAdmin and see what it tells you. Make sure to replace the variables history and id with an actual ID to test it...

Code: Select all

UPDATE colour_history SET history=trim('<INSERT_HISTORY_STRING_HERE>') WHERE id='<INSERT_ACTUAL_ID_HERE>';
Quick note, this is your exact query as posted inyour original post. Running it through phpMyAdmin will tell us if it is a query issue or a code issue.

Posted: Sun Jul 30, 2006 5:06 pm
by volka
$history has to be replaced as well.
It's probably easier to echo the statement.

Code: Select all

$history = nl2br($history);
$query = "UPDATE colour_history SET history=trim('$history') WHERE id='$id'";
echo '<fieldset><legend>Debug</legend>', htmlentities($query), '</fieldset>', "\n";
$Update = mysql_db_query ($dbname, $query) or die(mysql_error().': '.htmlentities($query));

Posted: Sun Jul 30, 2006 5:11 pm
by RobertGonzalez
I must have been editing when you made your post :oops: . Anyhow, either way, you should check to make sure it is a code issue before we get too deep into looking at code. Running the query through your db utility will tell you immediately if you have a SQL error. Once we know that we know how to plan our attack.

Posted: Sun Jul 30, 2006 5:27 pm
by volka
But copying code and replacing parts of it by what you think a variable's content is or may be ...that's way too much human interaction/intuition/wishing for my taste ;)
I'd rather stick with the autopilot

Code: Select all

$Update = mysql_db_query ($dbname, $query) or die(mysql_error().': '.htmlentities($query));
The code needs an error handler anyway. or die(mysql_error()) is a crude but simple one.

Posted: Sun Jul 30, 2006 5:31 pm
by RobertGonzalez
I agree.

Posted: Sun Jul 30, 2006 10:24 pm
by robster
Thanks so much everyone for this :)
I should have realised I showed the UPDATE code rather than the insert, but they both work the same way. Here is the SQL I use:

Code: Select all

$query = "INSERT INTO colour_history VALUES ('', '$user_id[user_id]', '$clientid', '$today','$history')";
So the ID, the userid, the clientid, date and of course, the history (which is the typed bit that is erroring) gets added all at the same time.


using volka's debug echo concept this is what I get:

Code: Select all

Debug
INSERT INTO colour_history VALUES ('', '1', '1', '2006-07-31','qwerty')
and then

Code: Select all

Debug
INSERT INTO colour_history VALUES ('', '1', '1', '2006-07-31','<qwerty>')
It's still doing the same thing though... Everything is added EXCEPT that history on the <qwerty> example, or anything where I put <> either side.

Does any of what I've now tested help? I'm still stumped, though I've learned a valuable debug method already :)


Rob

Posted: Sun Jul 30, 2006 11:27 pm
by RobertGonzalez
Is it not be inserted or not being displayed? I vaguely recall once a poster with the same issue as you except he was saying his data wouldn't display. What it ended up being was the browser trying to interpret <things_like_this> as an HTML tag.