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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

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

Post 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
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post 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.
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post 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
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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();
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

And all instances experience the same error?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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));
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I agree.
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Post Reply