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,

In reply to Perl/SQL syntax error by patt

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.