Perl/SQL syntax error

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

I'm having a problem with my MySQL syntax that I cannot resolve. Perhaps you could advise. The obvious purpose of this is to extract the source and target IP's of a communication from a Web Server log file, e.g. an Apache log file and insert this data with the time and date into a table. It is part of a forensic application I'm building. Java/C/Assembly I know, Perl/SQL I'm learning. The code I've written follows and the error msg is after that. My Code:

        $insertData = "INSERT INTO url_Connect (sourceIP,
                    dt,  tm,  remoteIP)  
        VALUES (
        $sourceIP, 
        $date,
        $time, 
        $targetIP
        )";
        $dbMake = $dbi->prepare($insertData);
        $dbMake->execute();                #<-----line 86
[download]

Command line error msg:

DBD::mysql::st execute failed: You have an error in your SQL syntax; c
+heck the manual that corresponds to your MySQL server version for the
+ right syntax to use near '192.168.1.1, 23/Mar/2008, 
                    04:02:02, 77.91.224.11)' at line 2 at createDB.pl 
+line 86
[download]

I have flagged line 86 above, as referred to in the error msg. 192.168.1.1 is a test value I inserted to allow the code succeed without error reporting on that value (please ignore it). What is puzzling me is the large gap in the data above between 23/Mar/2008, and 04:02:02 - the date and time. It appears to be either tabs or spaces, but I have checked the data collected by the regex's and neither tabs nor spaces are present in the data collected. Where does this come from, is this causing my error? The code I've used to create the table:

    my $createTable =     "CREATE TABLE IF NOT EXISTS
                          url_Connect(
                          sourceIP varchar(16) primary key,
                          dt date,
                          tm time,
                          remoteIP varchar(16)
                          )";
[download]

I've used regex's to extract the data needed to populate these fields, these are working perfectly with no surplus/excess white spaces. The rest of my code opens the logfile, reads every line and extracts the data I require. This executes without error. Thanks,

Comment on Perl/SQL syntax error Select or Download Code

Replies are listed 'Best First'.
Re: Perl/SQL syntax error by afoken (Chancellor) on Jul 04, 2009 at 11:06 UTC
Never, never, never paste values into SQL statements. Use placeholders. All DBI drivers support placeholders, even if the underlying database does not. Switch to placeholders and your problem is gone. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply]
Re^2: Perl/SQL syntax error by patt (Scribe) on Jul 04, 2009 at 11:21 UTC
Thanks for imparting your wisdom. This may seem lika a dumb question, but, can you enlighten me on what a placeholder precisely is. I've searched the MySQL docs and returned a blank. A web search is ambiguous, and more confusing. I did not print $insertData as suggested. I did not see the need to do so as all my data was correctly recovered by my regex's. I've just printed it now, and it's value is as given on the command line error report: `(192.168.1.1, 23/Mar/2008, 04:02:02, 77.91.224.11)` [download] This is just one line of data from a logfile of thousands of lines. I've used a selection of lines of the logfile as test data. I'd blow my command line up if I was to run the code over the entire logfile...and probably have to wait until next week for a result.	[reply] [d/l]
Re^3: Perl/SQL syntax error by morgon (Priest) on Jul 04, 2009 at 11:39 UTC
Your example using placeholders (or bind variables - they are the question marks): `my $sth = $dbh->prepare(<<sqlend insert into url_connect (sourceIP, dt, tm, remoteIP) values (?, ?, ?, ?) sqlend # and then: $sth->execute($sourceIP, $date, $time, $targetIP); # and again: $sth->execute($sourceIP2, $date2, $time2, $targetIP2);` [download] The idea is to prepare the query once and possibly execute it many times over, just supplying new values for the bind-variables. The overhead of preparing the query on the database-server(parsing the sql, checking persmission, generating data-access paths etc) then just occurs once (and this is essential for scalability on systems like Oracle or DB2).	[reply] [d/l]
Re^4: Perl/SQL syntax error by patt (Scribe) on Jul 04, 2009 at 13:54 UTC
Re^5: Perl/SQL syntax error by graff (Chancellor) on Jul 04, 2009 at 14:45 UTC
Some notes below your chosen depth have not been shown here
Re^4: Perl/SQL syntax error by patt (Scribe) on Jul 04, 2009 at 14:07 UTC
Re^5: Perl/SQL syntax error by kulls (Hermit) on Jul 07, 2009 at 14:39 UTC
Re: Perl/SQL syntax error by Anonymous Monk on Jul 04, 2009 at 10:45 UTC
My suggestion is to make your code print `$insertData` and then take that query and paste it into your command line tool for the database and debug it there.	[reply] [d/l]