MySQL question

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

I currently have a reporting cgi that takes html form output to perl cgi scripts that store the data into a flat file and e-mails the notification to distrobution lists.

I have been asked to expand this to not only cover my department but also some others in the company. To do this I need to import the data into a MySQL database. I wrote a short insert script that works for many of the entries, but any entry that contains cerain characters, like the ', it will errror out.

I have condensed the script to the offending section and included a CLI output for your parsing. Of course, db and server information has been changed to protect my company and myself.

Any assistance will be greatly appreciated.

###########################################################
#!/usr/bin/perl -w

use DBI;

my ($st) = 0;
if ( &db_connect() ) { 
    exit (1); 
}
print "Please insert some text:  ";
$sometext = <STDIN>;
chomp ($sometext);
$sql = qq { insert into my_table (update_1) values ('$sometext') };
$sth = $dbh->prepare($sql);
$sth->execute();
if ( $DBI::err ) {
    if ( $DBI::err == 1062) {
        print "\nDatabase server error: $DBI::err \n";
        print "Record already exists in database. \n";
        $st++;
    }
    else {
        print "Database server error: $DBI::err \n";
        $st++;
    }
}
$dbh->disconnect();

sub db_connect {
    my $user = "user";
    my $pass = 'password';
    my ($st) = 0;
    $dbh = DBI->connect("dbi:mysql:host=my.work.domain", $user, $pass)
+ or die "Database Connection not made: $DBI::errstr \n";
    if ( ! $dbh ) { 
        print "Error opening database: \n $DBI::err \n $DBI::errstr \n
+"; $st++;
    }
    $dbh->do("use mydb");
    return ($st);
}
############################################################
[download]

root@myserver scripts# perl migrate_data_short.pl
Please insert some text: An alarm was triggered for an OC-48 between Seattle (cr1.sea20) and Pullman, WA (PLMNWA-rtr2). Pullman shows having a redundant link out through Moscow, to Coeur d'Alane which then connects to Seattle (cr0.sea20). Heath at NTI was contacted to investigate the circuit.

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Alane which then connects to Seattle (cr0.sea20). Heath at NTI was contacted to ' at line 1 at migrate_data_short.pl line 14, <STDIN> line 1.
Database server error: 1064
root@myserver scripts#

If I omit the ' from "Coeur d'Alane" it posts just fine. How do I get chacters like this to be inserted to my db/table?

Comment on MySQL question Download Code

Replies are listed 'Best First'.
Re: MySQL question by almut (Canon) on Nov 28, 2008 at 14:06 UTC
Use placeholders — they'll take care of such issues... (in addition to that, they'll also prevent potential SQL injection problems, in case the user input should come from untrusted sources...)	[reply]
Re: MySQL question by bradcathey (Prior) on Nov 28, 2008 at 14:15 UTC
What almut is talking about: `#instead of: $sql = qq { insert into my_table (update_1) values ('$sometext') }; $sth = $dbh->prepare($sql); $sth->execute(); #use placeholders: $sql = qq { insert into my_table (update_1) values (?) }; $sth = $dbh->prepare($sql); $sth->execute($sometext);` [download] First, it will do all the escaping for you, second, will protect your script against injection attacks. BTW, if you are going to do a lot of this, you should look at a framework like CGI::Application that does a lot of the heavy lifting for you. —Brad "The important work of moving the world forward does not wait to be done by perfect men." George Eliot	[reply] [d/l]
Re^2: MySQL question by Dranzaz (Sexton) on Nov 28, 2008 at 14:34 UTC
Thanks to all above. The shortened script works. Will now be updating the larger read and insert script and am crossing my fingers. I had read the documentation on placeholders but was not keen on their use. Thank you for the direct examples, they now make sense.	[reply]
Re^3: MySQL question by lostjimmy (Chaplain) on Nov 28, 2008 at 15:07 UTC
In addition to what everyone else has said, placeholders are also good for performance. Instead of preparing the same statement handle over and over again (when inserting multiple rows), it only needs to be done once. They also make the code a lot cleaner, making it more obvious which values are supplied and which are static.	[reply]
Re: MySQL question by tirwhan (Abbot) on Nov 28, 2008 at 14:11 UTC
Don't put variables into your SQL statement directly, use placeholders (see DBI for the many reasons why). `... $sql = qq { insert into my_table (update_1) values (?) }; $sth = $dbh->prepare($sql); $sth->execute($sometext); ...` [download] All dogma is stupid.	[reply] [d/l]
Re: MySQL question by Dranzaz (Sexton) on Nov 28, 2008 at 14:04 UTC
I added the following line after the "chomp": `$sometext =~ s/\'//g;` [download] This removes the ' and allows the entry to be posted to the db/table. This is not ideal as I need quotations to be preserved. thank you	[reply] [d/l]