unpacking - html re-encoding?

[Mac]

Probably a simple question but, I have a form where a user can place a job item, for example. Details of this job are stored in a mysql database. If you can imagine, the user submits a job containing:

he can't wait so he ran...

it would do an insert such as..

insert into jobs values ('he can't wait so he ran'); which is definatly not desierable.

How can you convert special characters into HTML format

ie
he said, "I'LL be back"
==
he+said%2c+%22I%27LL+be+back%22

Is there an easier way round this? Like parse html (what is parsehtml - what does it do)? also, more simple stuff... I want to check if a date matches a certian format (mysql problem again) so I want to check if $date =~ /0000-00-00/ where $date can be like 2000-05-23 (CCYY-MM-DD)

please email me, [e-mail] as I'm new to this site and probably won't be able to find my way bacdk here...

[alfie]

You might find the quotemeta function quite useful - it's exactly for this purposes.

Update: Alright - I shot before finish reading :/
About converting special characters: The CGI module gives you the function you need to do that.
About the date-check: m/\d{4}-\d{2}-\d{2}/ should do the trick - read perlre for why.

And finally, about mailing you: Sorry, I don't do that stuff. If one is searching for help in a special place s/he should also check back there for the answers - that's the way a community works, by sharing the thoughts. Especially on topics like this. It is easy that I might have missed the one or the other thing (like simply stated by this update ;) - others might add to it. That's not possible if I would have mailed you.... Think of it.
--
use signature; signature(" So long\nAlfie");

[iakobski]

quotemeta is close but not a perfect fit. It will escape (with a backslash) any non-alphanumeric which includes ALL punctuation and even spaces.

In most dialects of SQL, quotes are escaped by doubling them:

he said, "I'LL be back"

Would need to become:

'he said, "I''LL be back"'

Using the example given by the questioner. There's no need to escape other punctuation or spaces. And I said most, because the quoting given by DBI::quote() will match that required by the database transparently.

Update OK, alfie so you updated your reply while I was writing that. And ++ for the bit about asking for an e-mail.

-- iakobski

[tachyon]

This sub does the encoding you want. It will encode any char not in the negated chatacter class. Just drop any chars you want encoded from this class (I've dropped the ' for you already :-) I've included a decode sub as well.

sub encode {
    my $encode = shift;
    $encode =~ s/([^\w\s.!~*()-=&])/sprintf "%%%02X", ord($1)/eg;
    $encode =~ tr/ /+/;
  return $encode;
}

sub decode {
    my $decode = shift;
    $decode =~ tr/+/ /;
    $decode =~ s/%([a-f\d][a-f\d])/pack("c",hex($1))/ige;
  return $decode;
}

$str = qq/he said, "I'LL be back"/; 
$enc = encode ($str);
$dec = decode ($enc);
print "$enc\n$dec";

Hope this helps

cheers

tachyon

s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

[iakobski]

Can you confirm how you are the DBI module to do the insert into the database?

If you are there are two ways in which these sorts of things are handled. If you are binding parameters to statements, using bind_param() then the quoting is handled automatically. If you are using a variable in the SQL statement, then there is the handy quote() function:

use strict;
use DBI;

# set up variables stuff omitted

my $dbh = DBI->connect( $connection_string, $user, $pass );

my $value_q = $dbh->quote( $value );

my $sth = $dbh->prepare( "INSERT INTO my_table VALUES 
                         ( $value_q ) " );

## etc...

If you're not using DBI or DBIx, then you should check them out.

-- iakobski

[tachyon]

Here is a date chcking routine for you. It will allow 1 or 2 digit entries for day and month to avoid leading 0 requirement. If you want to insist on two digits remove the question marks.

$date = "2000-12-1";

unless ($date =~ m/(\d\d\d\d)-(\d\d?)-(\d\d?)/) {
    die "Invalid date format\n";
}

# put captured data from regex into appropriate vars
my ($year,$month,$day) = ($1,$2,$3);
die "Invalid year!" if $year < 1900 or $year > 2100;
die "Invalid month!" if $month < 1 or $month > 12;
die "Invalid day!" if $day <1 or $day > 31;

print "Date OK!";

The date checking is rough and ready as it does not check for leap years and allows for instance 31 days in February!. You can easily fix this by either coding some decent checking or using the check_date() function of the Date::Calc module.

cheers

tachyon

s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print