INSERTing into a MySQL Database using DBI/Perl

Now we are going to talk about INSERTing information into your MySQL table!

This is actually the trickiest function and the one most likely to bomb your program if you don't take a few precautions. If you attempt to insert a value that is too large for the field, you will get an error. If you attempt to insert unquoted text, you will get an error. If you try to quote a value going into an integer field. . . well, you get the picture.

INSERTING TEXT
Every database is different in how it handles the quoting of text. An Oracle DB might require inserted values to be placed in double-quotes ("text") and MySQL requires entries to be placed in single quotes ('text'). Since the rules are different from DB to DB, and DBI was created to be database independent, they built in a handy little feature that quotes your text correctly depending on which database type you are talking to.

Let's say that I wanted to insert this value into my database:

$input_value="Some text, to insert!";

If I attempted to INSERT that directly into the database, the script would bomb out because it would interpret the value of $input_value like this:

What's in your script
$sth=$dbh->prepare("INSERT INTO my_table (field_1) VALUES ($input_value) ");
What actually happens
$sth=$dbh->prepare("INSERT INTO my_table (field_1) VALUES (Some text, to insert!) ");

I need to massage my input into a format that the database can understand, and because this function is database specific, I will make a reference to my database handle ($dbh).

$quote_value=$dbh->quote($input_value);

This is what happens when I run this with my MySQL database:

What's in your script
#the field definition of field_1 is TEXT
$sth=$dbh->prepare("INSERT INTO my_table (field_1) VALUES ($quote_value) ");
What actually gets executed
$sth=$dbh->prepare("INSERT INTO my_table (field_1) VALUES ('Some text, to insert!') ");

The program doesn't choke on this and I am a happy camper. I can see you thinking to yourself, "AHA! But what if my text has single-quotes (') INSIDE the text!?" To be honest with you, even with the $dbh->quote(), I have had scripts bomb if the input was constructed with a bunch of single and double quotes. My thinking is that this is still a relatively new programming interface and it still has some ironing to go through. Just pay more attention to the text that is coming in from the user and take care to remove any naughty symbols before attempting an INSERT. You should be filtering and cleaning that text anyway : )

INSERTING NUMBERS
Numbers are easier and harder to INSERT into your database. You do not need to quote your numbers (in fact, your program will bomb if you try to), BUT you must take care in your script to make sure that you are ONLY inserting numbers into those fields. Any non-number will bomb your script, so be sure to strip out any user-input naughty bits with a substitution:

$INPUT{'user_input'}=~tr/0-9/ /c; #replace any non-number with a space
$INPUT{'user_input'}=~s/ //g; #drop the spaces

Then perform the INSERT #the field definition of field_2 is INT(9)
$sth=$dbh->prepare("INSERT INTO my_table (field_2) VALUES ($INPUT{'user_input'}) ");

Of course, after PREPARING your database query, you need to EXECUTE it:

$sth->execute();

There are two more types of values that you need to worry about in your INSERT statements. One is in the case of an auto_increment field that is set to NOT NULL. You would use this to create a unique transaction ID. To make the database generate the next transaction ID, you just insert NULL (all caps, no quotes) into that field.
Number two pops up when you are dealing with dates and times. If your field is set up with a TIMESTAMP and NOT NULL as it's field definition, you can again insert a NULL and it will automatically be set to whatever the current time is. Different databases handle their timestamps differently, so I'll let you refer to you documentation for your specific DB. In MySQL, there is a DATE definition that I like a lot. What if the field definition is not set to NOT NULL and I want to put the current date in? I insert 'current_date', the database will put the current date in the field with the correct format.

I learn best by seeing examples of how other people do things, so here is an example INSERT. This example covers everything I've talked about here.

$varname1=$dbh->quote($INPUT{'owner'});
$varname2=$dbh->quote($INPUT{'contact'});
$varname3=$dbh->quote($INPUT{'email'}) ;
$varname4=$dbh->quote($INPUT{'phone'}) ;
$varname5=$dbh->quote($INPUT{'company_listed'}) ;
$varname6=$dbh->quote($INPUT{'descrip_short'}) ;
$varname7=$dbh->quote($INPUT{'descrip_long'});
$INPUT{'annual_rev'}=~tr/0-9/ /c;
$INPUT{'annual_rev'}=~s/ //g;
$varname8=$INPUT{'annual_rev'};
$sth=$dbh->prepare("INSERT INTO big_table (trans_id,owner,contact,email,phone,company_listed,descrip_short,descrip_long,annual_rev,trans_date) VALUES (NULL,$varname1,$varname2,$varname3,$varname4,$varname5,$varname6,$varname7,$varname8,current_date)"); $sth->execute() or warn "Can't execute statement $DBI::errstr \n"; # Make it so

NOTES:
"$varname4=$dbh->quote($INPUT{'phone'});"? Why isn't this an INT field?
If you try to put in a phone number like this 919-968-9845 into an INTeger field without quoting, the script will actually attempt the mathematical operation on the value before inserting it. You would actually end up inserting -9894 into the table. I banged my head against the wall on this one.

In the above example, this is how I created the table in MySQL:

create table big_table (
trans_id int(10) not null auto_increment,
owner VARCHAR(30),
contact VARCHAR(30),
email VARCHAR(50),
phone VARCHAR(30),
company_listed VARCHAR(75),
descrip_short VARCHAR(250),
descrip_long text,
annual_rev DECIMAL(14,2),
trans_date DATE,
primary key (coid));

I think that pretty well covers it. There are a lot of nuances and specific situations, but remembering to quote your text input and remembering to NOT quote your numeric input will go a long way toward making your script operational.

As always, I welcome your feedback and any questions you have about this example. richard@oakbox.com