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

hi, I am trying to run an INSERT command from Perl, and encounter the following error: 
DBD::mysql::st execute failed: Query was empty at otest.pl line 56.
SQL error:$DBI::errstr

[download]
so, I wrote a print command to see the output on my screen and that produces the results fine. here's the print and the insert command....I suspect I might not be using the right syntax in my INSERT command....can you please help me fix it/point me in the right direction...thx! 
print "Symbol: $h1{'symbol'}\tExpn_date: $exp\tStrike: $h1{'strike'}\t
+Bid: $h1{'bid'}\tAsk: $h1{'ask'}\tLast: $h1{'last'}\tOpen: $h1{'open'
+}\tVolume: $h1{'volume'}\n ";

my $ins_rec="INSERT INTO $table (symbol,expn_dt,strike,open,bid,ask,la
+st,volume) VALUES(\'$h1{'symbol'}\',\'$exp\',\'$h1{'strike'}\',\'$h1{
+'open'}\',\'$h1{'bid'}\',\'$h1{'ask'}\',\'$h1{'last'}\',\'$h1{'volume
+'}\');";

[download]
Thank you for your replies. Dave, I did use DBI 
my $dbh=DBI->connect($dsn,$user,$pwd) || die "could not connect to dat
+abase:$error\n";

[download]
NetWallah,this is the code for the INSERT string being applied to the database. 
  my $sth=$dbh->prepare($ins_rec);

     $sth->execute || die "SQL error:$error\n";

[download]
When I print $ins_rec, I get a blank... <code> my ins_rec is DBD::mysql::st execute failed: Query was empty at otest.pl line 57. SQL error:$DBI::errstr <code>

Replies are listed 'Best First'.
Re: MySQL INSERT in Perl
by davido (Cardinal) on Aug 04, 2011 at 04:54 UTC

    When you print $ins_rec to your screen what do you get? Could you post the exact output of printing $ins_rec?

    You should be using placeholders. Use DBI, prepare the statement, and then execute it by including the bind values as $sth->execute() bind params.

    What I expect to find when you show us the contents of $ins_rec is something in your big string full of interpolation sending an unescaped metacharacter to MySQL that corrupts the SQL query statement. That, along with the potential for intentional SQL injection attacks is why Placeholders and Bind Values exist (well, efficiency and convenience too).

    Per your OP update: I realize you're using DBI, but you may not be using it safely. You have to either use DBI's quote() method on the data you're instering, or you rework your prepare() to use placeholders, and your execute() to provide the bind values.


    Dave

[reply]
[d/l]
[select]
      hi Dave, I've updated the code and $ins_rec prints nothing
[reply]

        "I've updated the code and $ins_rec prints nothing"

        That's a big problem, right? So you now have two jobs:

        First: find out why $ins_rec is empty when you try to send an INSERT to MySQL.

        Second: Convert your program to use placeholders so that you are not subjecting yourself to possibly corrupt SQL or SQL injection attacks due to unescaped metacharacters.


        Dave

[reply]
Re: MySQL INSERT in Perl
by NetWallah (Canon) on Aug 04, 2011 at 04:55 UTC
    I don't see anything wrong - just stuff I'd write differently - probably something like: 
    my $ins_rec="INSERT INTO $table (symbol,expn_dt,strike,open,bid,ask,la
+st,volume) VALUES('"
    . join( "','",  map {$h1{$_}} qw| symbol strike open bid ask last 
+volume   |). "')";

    [download]
    You have not showed HOW this insert string is applied into the database.

    That is where I suspect the problem is. Please show more of the relevant part of the code.

                "XML is like violence: if it doesn't solve your problem, use more."

[reply]
[d/l]

      Be careful! Not only is your data susceptible to broken SQL statements, it's highly vulnerable to SQL injection attacks. At least run your data through quote (but use placeholders instead).

[reply]
[d/l]
      use SQL::Abstract;
my $SQL = SQL::Abstract->new;

...

my ($sql, @bind) = $SQL->insert ($table, \%h1);
$dbh->do ($sql, {}, @bind);

      [download]
[reply]
[d/l]
        thx superdoc, I'm not sure I fully understand how that will work... also, it seems like I should replace prepare/execute with do()?
[reply]
      hi NetWallah, I've updated the post with the execute commands et al
[reply]
Re: MySQL INSERT in Perl
by Anonymous Monk on Aug 04, 2011 at 06:25 UTC
[reply]
Re: MySQL INSERT in Perl
by NetWallah (Canon) on Aug 04, 2011 at 14:36 UTC
    Please don't edit your original post without any indication of the edit.
    It confuses people trying to follow a thread.

    Did you "use strict;" ? It seems like $ins_rec may have gone out of scope by the time you called the "prepare".

    Also, I acknowledge the excellent suggestions by others to use placeholders, to avoid sql-injection, as well as better ways to compose the query.

    If you use the perl debugger, you could step you way to right before the prepare, and view the content of $ins_rec, at that point.

                "XML is like violence: if it doesn't solve your problem, use more."

[reply]
      I did use

      use strict;

      unfortunately, when I use it, I get the following error: 
      Global symbol "$ins_rec" requires explicit package name at otest.pl li
+ne 53.
Global symbol "$ins_rec" requires explicit package name at otest.pl li
+ne 55.
Execution of otest.pl aborted due to compilation errors.

      [download]
      so I commented it out... how does $ins_rec go out of scope?
[reply]
[d/l]
        OK - strict, did identify your problem, which does indeed show a scoping issue.

        Do you have BLOCKS in your code - i.e - segments of code enclosed in {curly braces} ?

        Is the "my $ins_rec" line inside one of these blocks ?
        If so, that variable is not visible outside the block - you need to move the "my" statement outside the block.

        Please do yourself a favour and ALWAYS "use strict;" and "use warnings;". Professional programmers recommend this for very good reasons.

                    "XML is like violence: if it doesn't solve your problem, use more."

[reply]

Back to Seekers of Perl Wisdom