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

Hello monks

This task should be trivial...but when I insert/update a record in my MySQL database with a value containing special characters, the special characters are not saved. No special message. Why?

#shortened script #getting pars with CGI my $q = CGI->new(); my $LicenseKey = $q->param('key'); my $UserName = $q->param('user'); my $UserPW = $q->param('pw'); my $dbhServer = DBI->connect("DBI:mysql:$db:$host", $user, $pass); my $sth = $dbhServer->prepare("INSERT INTO Accounts VALUES (?, ?, ?, ? +, ?)"); $sth->execute(undef, $LicenseKey, $UserName, $UserPW, $date) || print +("ERROR EXECUTION: " . $dbhServer->errstr); #same with updating my $sth = $dbhServer->prepare('update Accounts set UserName=?, Passwor +d=? WHERE LicenseKey=?'); $sth->execute($UserName, $UserPW, $LicenseKey)|| print ("ERROR EXECUTI +ON: " . $dbhServer->errstr); $dbhServer->disconnect;

Replies are listed 'Best First'.
Re: CGI MySQL insert/update special characters
by Your Mother (Archbishop) on Mar 28, 2020 at 15:30 UTC

    Probably all you need is to start with use CGI -utf8; to decode binary data in parameters to characters; CGI has info about what it does. But that does assume your DB is correct, which is rarely the case with default MySQL, and I believe there is a security issue with file uploads if youíre not careful with that default but I canít cite it. *Every* part of the interchange has to be right and guessing or just trying to patch stuff in one point is a losing game. Try to pick up as much as you can out of tchristís canonical answer to these issues:

      Thank you, Your Mother. I certainly gave too little details. I solved the issue. MySQL was set correctly. I was passing the values with a GET and something like this: /cgi-bin/mobile/$LicenseKey&user=$CloudUserName&pw=$CloudUserPW This would have needed special care for special characters. So I simply switched to POST and sending my data with:

      my $request = POST($url, Content_Type=>'application/json', Content => encode_json($data) ); my $response = $ua->request($request);

      This works fine. Will now read on vulnerabilities of this approach.

        Your new approach using POST is far safer than what you were doing and has far better compliance with RFC2616's rules for "safe" and "idempotent" methods. In brief, GET requests are "defined" to not have side effects, while other methods, including POST, have no such restrictions. You must use POST if the request is intended to do something.

        Further, request URLs often appear in logs, but POST data is generally understood to be potentially sensitive. You should never submit a password with a GET request; logins need to always use POST and, if you are sending them over the open Internet, TLS. Plaintext HTTP is safe on an isolated network, but sending anything remotely sensitive over the open Internet needs HTTPS (or the TLS upgrade sequence for HTTP/TLS over port 80).