DBI/MySQL system cutting off UPDATE values.

punch_card_don has asked for the wisdom of the Perl Monks concerning the following question:

Meaningful Monks,

Is it my Perl. It is my SQL? Is it MySQL? Inquiring minds want to know.

I have a user's info table in a MySQL db. One of the columns - surprise surprise - is the phone number, 'phone'. It's declared as VARCHAR(20). Input comes from an html form. The phone number goes into a text input, imaginatively named 'phone', which, using cgi.pm, gets read into a hash of values, $form_values{'phone'}.

Here's the strange part: certain formats of phone number input result in the the last one, sometimes two characters being cut off in the db.

For example:

Enter 123-456-7890 into the form, and everything goes perfect : 123-456-7890 shows up in the user's record.
Enter (123) 456-7890, and it's (416) 456-78 that gets stored.
Enter 123-456-7890-0000, and it gets stored properly - so it's not a question of string length
Try various different combinations of characters inserted in the string, (, ), |, , , etc, and some combinations produce the correct storage, some don't, but it's always consistent - a combination that doesn't work always doesn't work.

In the Perl script, the pertinent lines are:

$sql = "UPDATE users SET
                            fname='".$form_values{'fname'}."',
                            lname='".$form_values{'lname'}."',
                    phone='".$form_values{'phone'}."'

    WHERE
                            (contact_id = $form_values{'contact_id'})
                        ";
print "<p>sql = $sql\n";
#execute
$sth = $dbh->prepare($sql) or die("Could not prepare!" . $dbh->errstr)
+;
$sth->execute() or die("Could not execute!" . $dbh->errstr);
[download]

The SQL always look well formed. The phone number input to the form always shows up in the sql-statement.

Holy weapons of mass destruction, Batman, what could be blowing up in this?

Thanks.

Forget that fear of gravity,
Get a little savagery in your life.

Comment on DBI/MySQL system cutting off UPDATE values. Download Code

Replies are listed 'Best First'.
Re: DBI/MySQL system cutting off UPDATE values. by Joost (Canon) on Jan 31, 2008 at 20:54 UTC
Ouch. Don't do that. What if the $fname value contains a quote? Or some other "special" character you haven't thought of yet? Use placeholders (or use `$dbh->quote` if placeholders are underpowered). see this section of the DBI docs for starters "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply] [d/l]
Re: DBI/MySQL system cutting off UPDATE values. by nedals (Deacon) on Jan 31, 2008 at 22:39 UTC
Do you really mean... Enter (123) 456-7890, and it's (416) 456-78 that gets stored. I assume that'a typo and it should be (123) not (416). Sounds like you are somehow adding a bunch of leading spaces when using parens. How do you get your form data? Are you using the CGI.pm module or did you roll-your-own. I suspect the latter since I see a %form_values hash (but I could be wrong) The phone number input to the form always shows up in the sql-statement. Are you sure? Remember you cannot see leading spaces if you are printing the SQL to a webpage unless you check the source. As suggested by Joost, use placeholders and use the CGI module if you are not already doing so.	[reply]
Re^2: DBI/MySQL system cutting off UPDATE values. by punch_card_don (Curate) on Jan 31, 2008 at 23:08 UTC
Yes - you're right - typo. Should read: Enter (123) 456-7890, and it's (123) 456-78 that gets stored. That is, the '90' at the end gets cut off. But the real insight in your post is here: The phone number input to the form always shows up in the sql-statement. Are you sure? Remember you cannot see leading spaces if you are printing the SQL to a webpage unless you check the source. Must escape tunnel vision - yes, outputting the sql to a browser - check the source - it's not space, it's a little function I forgot about that uses HTML::Entities to convert all non-alphanumeric characters into entities in the input -exactly for the reasons Joost mentioned. Of course, '(123)' becomes '& # 4 0 ; 1 2 3 & # 4 1 ;', (spaces added to display code) and there go 10 of my alloted 20 characters for enitity codes - and by strange coincidence the input has been concatenated to 9 numbers and a dash. So it turns out this was the right place to post this - it was my Perl. Thanks a bunch.	[reply]