Error adding a record into a MySQL table: 'Unknown column ...'

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

Having some trouble first time using MySQL from Perl. Here are the few lines I'm trying to add an entry into my database and it isnt working. Heres the top few lines of the script and the few lines causing problems:

#=== Top portion
#!/usr/bin/perl -w

use strict;
use DBI;
use CGI;

#=== rest of code
$tmpStr = "INSERT INTO employee (EMPLOYEE.ID, EMPLOY.FIRST.NAME, EMPLO
+Y.LAST.NAME, ADDRESS.LINE.1, ADDRESS.LINE.2, ADDRESS.LINE.3) " .
                           "VALUES ('1000', '".$q->param("EMPLOY.FIRST
+.NAME")."', '".$q->param("EMPLOY.LAST.NAME")."', '".
                           $q->param("ADDRESS.LINE.1")."', '".$q->para
+m("ADDRESS.LINE.2")."', '".$q->param("ADDRESS.LINE.3")."')";
        $queryObj = $db_handle->prepare($tmpStr);
        $queryObj->execute or die "Could not execute command";
[download]

Here's the error :
Use of uninitialized value in concatenation (.) or string at C:\Inetpub\wwwroot\cgi-bin\EmployMtce.pl line 13. DBD::mysql::st execute failed: Unknown column 'EMPLOY.FIRST.NAME' in 'field list' at C:\Inetpub\wwwroot\cgi-bin\EmployMtce.pl line 17. Could not execute command at C:\Inetpub\wwwroot\cgi-bin\EmployMtce.pl line 17.

2006-09-09 Retitled by Arunbear, as per Monastery guidelines
Original title: 'MySQL'

Comment on Error adding a record into a MySQL table: 'Unknown column ...' Download Code

Replies are listed 'Best First'.
Re: Error adding a record into a MySQL table: 'Unknown column ...' by Joost (Canon) on Sep 09, 2006 at 16:40 UTC
You probably don't have a column named "EMPLOY.FIRST.NAME" in your EMPLOYEE table (Note the "EE" at the end of the table name) By the way, it's probably not a good idea to use dots in column names at all, since they're already used to seperate schemas / tables / columns in SQL. Also, one of your parameters contains an undefined value, and it's a really bad idea to just insert user input into your SQL, since that makes it really easy for someone to crash your query and/or run their own SQL queries (like "DROP TABLE employee". :-) You should use placeholders and/or quote. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply]
Re: Error adding a record into a MySQL table: 'Unknown column ...' by merlyn (Sage) on Sep 09, 2006 at 16:39 UTC
You should definitely be using placeholders instead of this. You're very ripe for an SQL Injection attack. -- Randal L. Schwartz, Perl hacker Be sure to read my standard disclaimer if this is a reply.	[reply]
Re: Error adding a record into a MySQL table: 'Unknown column ...' by blue_cowdawg (Monsignor) on Sep 09, 2006 at 17:28 UTC
Building on what other monks have already stated, here is a pattern for you to use that might help with what you are trying to accomplish. I'm making a few assumptions here, but here goes: `my $queryObj = $db_handle->prepare( qq( insert into employee(id,first_name,last_name, address.line.2,address.line.3) values (?,?,?,?,?,?) ) ) or die "Could not prepare query: ".$db_handle->errstr;` [download] Assuming you don't die due to syntax error, bad column names or other problems your next step somewhere in code would be `$queryObj->execute( ...insert value list here ... ) or die "Could not execute " . $queryObj->errstr;` [download] As merlyn pointed out, beware of SQL injection attacks. Before I pass data from a CGI form I'm going to do some cursory validation at the very least if not stringent validation to make sure I'm getting back in my CGI input what I expected and not bad stuff. Also, using placeholders and/or variable binding makes use of some of the strengths of using DBI in the first place. Otherwise it isn't worth the overhead. Peter L. Berghold -- Unix Professional Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg	[reply] [d/l] [select]
Re: Error adding a record into a MySQL table: 'Unknown column ...' by jZed (Prior) on Sep 09, 2006 at 16:43 UTC
Assuming that line 13 is the line where you assign $tmpStr and that line 17 is the line where you execute the $queryObj (it would have been nice if you'd told us the line numbers), then the unitialized value is probably from missing CGI parameters (e.g. they were left blank when the form was submitted or they are not spelled correctly). The unkown column error is pretty self explanatory ... did you perhaps mean to have an underscore instead of period between FIRST and NAME? Once you get those issues resolved, you should read the DBI docs on placeholders because putting your CGI parameters directly in the SQL like you do is not a good idea.	[reply]
Re: Error adding a record into a MySQL table: 'Unknown column ...' by stanleyscup (Novice) on Sep 09, 2006 at 18:01 UTC
The period removed did it. Thanks. It's been a very long time since using Oracle (7) so just getting back into SQL crap. Anyone in here use uniVerse before? I like that database just there isn't much support out there yet. Thanks again guys/gals.	[reply]
Re: Error adding a record into a MySQL table: 'Unknown column ...' by stanleyscup (Novice) on Sep 09, 2006 at 17:54 UTC
Thanks all for the comments. I'll try renaming the properties to have underscores instead of periods then. By the way, this is really just a program whereby I'm learning to connect and play with MySQL and isn't an app for the web so checking for attacks and so forth I wasn't worried about. I'll try what I mentioned above and let you know what I find. Thanks again.	[reply]