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

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.

Comment on Re^4: perl, mysql: "fetchrow_array failed: fetch() without execute()"

Replies are listed 'Best First'.
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]