Re^2: perl, mysql: "fetchrow_array failed: fetch() without execute()"

This sequence of statements can be easier:

$query = "select * from finalLevel3 where id=\"$ID\";";
$sth = $dbh -> prepare($query);

# I would suggest this:
my $query = $dbh->prepare("SELECT * FROM finalLevel3 WHERE id='$ID'");
[download]

I personally like to captialize the SQL key words, but preferences vary. Also the single quote within the double quote will expand $ID just fine. There is no need for \" in this situation. I was amazed when I learned this.

I am also a bit confused about this:

 foreach $item (@{$clusters}) {
 #.....
 }
[download]

I would think that since you are iterating over clusters, that something like this would be more clear:

 
foreach my $cluster (@$clusters) {                                 
#.....
}
[download]

So when do you need the extra {}? You need this when dereferencing an indexed expression, but not a non-indexed one. You also need the extra {} to make a list from a list ref returning function.

 
my @list = @{listRefReturningFunction()};
#also consider:
my $r = {"a" => [1,2,3,4],
         "b" => [5,6,7,8],
        };
print @{$r->{"a"}}; #prints 1234
                    #here print @$r->{"a"} won't work
                    #because this is a subscripted expression
# now with list...
my @x =(1,2,3,4);
my $xref=\@x;
print @$xref;       #prints 1234 also
                    #this works because there is no subscript
[download]

Comment on Re^2: perl, mysql: "fetchrow_array failed: fetch() without execute()" Select or Download Code

Replies are listed 'Best First'.
Re^3: perl, mysql: "fetchrow_array failed: fetch() without execute()" by ikegami (Patriarch) on Dec 29, 2008 at 10:18 UTC
Did you really mean to reply to my post? It seems you ignored everything I said. This sequence of statements can be easier: `my $query = $dbh->prepare("SELECT * FROM finalLevel3 WHERE id='$ID'");` Just adding quotes around the contents of $ID doesn't properly convert it from arbitrary text into an SQL string literal. It's an bug or injection attack waiting to happen. It's also very inefficient. You're compiling the query over and over and over again. The following is a whole world better: `# Outside of loop my $sth = $dbh->prepare("SELECT * FROM finalLevel3 WHERE id=?"); $sth->execute($ID);` [download] I would think that since you are iterating over clusters, that something like this would be more clear: If you look closely, you'll notice he's actually iterating over the cluster indexes, so the following would be even better: `for my $i (0..$#$clusters)` [download] So when do you need the extra {}? You need this when dereferencing an indexed expression, but not a non-indexed one. `@{ foo() }` would also need it. The correct answer would be "Pretty much anything other than a variable name" or maybe even "Everything other than a variable name".	[reply] [d/l] [select]
Re^4: perl, mysql: "fetchrow_array failed: fetch() without execute()" by Marshall (Canon) on Dec 29, 2008 at 13:46 UTC
Ok, I maybe I hit a wrong button and replied at the wrong level. I am a "new guy" in this forum and I say, please don't take offense! None at all was intended! I only had two main points: 1) simple SQL statements - unnecessary escapes like: \" 2) simplify de-referencing a list ref I hear: " Just adding quotes around the contents of $ID doesn't properly convert it from arbitrary text into an SQL string literal... " From my experience so far, "prepare" appears to do exactly that! The prepare method is much smarter than I would have thought. This is what amazed me! And I mentioned this in my post. This thing gets a string from Perl and then applies its own "rules". How "prepare" gets the "real" value of $ID is a mystery to me. But it does! I have several programs that work with this syntax. As far as de-referencing lists, we are "on the same page.	[reply]
Re^5: perl, mysql: "fetchrow_array failed: fetch() without execute()" by DStaal (Chaplain) on Dec 29, 2008 at 14:26 UTC
I hear: " Just adding quotes around the contents of $ID doesn't properly convert it from arbitrary text into an SQL string literal... " From my experience so far, "prepare" appears to do exactly that! The prepare method is much smarter than I would have thought. This is what amazed me! And I mentioned this in my post. This thing gets a string from Perl and then applies its own "rules". How "prepare" gets the "real" value of $ID is a mystery to me. But it does! I have several programs that work with this syntax. It does work, usually. But it is an error waiting to happen. There is no magic involved: Perl is simply interpolating the variable, and putting it's value into the location in the string. Which sounds fine. Until someone manages to figure out how to have `$ID = q[1'; DROP TABLE finalLevel3 ']`, in which case you are in deep trouble, because Perl will gladly send the perfectly correct SQL (well, if I've written it right...) to your database, which will nicely drop the table for you. All without any errors. Contrast that to using placeholders: Now the value of $ID is passed as a value (not as part of the query string), and the database will get it as a chunk. You'll get an error saying `q[id "1'; DROP TABLE finalLevel3 '" not found]`, but your database will still be intact.	[reply] [d/l] [select]
Re^5: perl, mysql: "fetchrow_array failed: fetch() without execute()" by runrig (Abbot) on Dec 29, 2008 at 17:01 UTC
How "prepare" gets the "real" value of $ID is a mystery to me. But it does! I have several programs that work with this syntax. It's not prepare that does the magic voodoo, it's the double quotes (and we like to call it interpolation). You really ought to understand the "mystery", especially if you are doing web programming or any other code that allows arbitrary user input. If you are going to interpolate variables into a SQL string, at least do it the "right" way: `my $q_ID = $dbh->quote($ID); my $sth = $dbh->prepare("select foo from bar where baz = $q_ID");` [download] In general though, it's better to use placeholders.	[reply] [d/l]