Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Multiple SQL statements in DBI

by tel2 (Scribe)
on Sep 07, 2012 at 07:16 UTC ( #992255=perlquestion: print w/ replies, xml ) Need Help??
tel2 has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

I'm trying to see if I can run 2 commands in 1 with Perl's DBI module.  Here's my code:

$dbh->do("UPDATE person SET nickname = 'one' WHERE id=1") or die "Coul +d not execute #1: $DBI::errstr\n"; $dbh->do("UPDATE person SET nickname = 'two' WHERE id=1; UPDATE person + SET nickname = 'three' WHERE id=1") or die "Could not execute #2: $D +BI::errstr\n";
The output is: "Could not execute #2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE person SET nickname = 'three' WHERE id=1' at line 1"

The result is, nickname ends up being = 'one'.

Questions:

1. If the error is with setting nickname = 'three', then why doesn't nickname end up being = 'two'?

2. Does DBI allow multiple SQL statements to be run in one invocation?  If so, how can that be done?

3. Or if not, why not? Is it to help prevent SQL injection, perhaps?  That's fine, as that's the reason for my test, but I'd like to see how to make it happen before I see if my methods of prevention are working.

Thanks.
tel2

Comment on Multiple SQL statements in DBI
Download Code
Re: Multiple SQL statements in DBI
by davido (Archbishop) on Sep 07, 2012 at 08:24 UTC

    What are you actually trying to accomplish? I mean what do you want to gain by running two statements in a single call? Are you trying to achieve an atomic operation? Some efficiency issue? Or just trying to save a few keystrokes?

    Depending on the answer to that question, we can probably help guide you to an actual solution.


    Dave

      Sorry Dave - I should have made that clearer.  My reason is (hidden) in question 3.

        Ah, in that case:

        Placeholders and bind-values are what you should be using. They would prevent the possibility of an SQL injection attack. Even if the semicolon isn't the issue, there are other things user-supplied input could do when interpolated into the middle of an SQL statement. But placeholders eliminate the interpolation, and overcome that issue. While you might not be able to construct an attack with a semicolon, I wouldn't be too confident that you've eliminated all attack vectors. At least with placeholders you can cross the SQL injection attack off the list.


        Dave

Re: Multiple SQL statements in DBI
by Corion (Pope) on Sep 07, 2012 at 08:29 UTC

    Your question 1 is answered by the likely fact that the statement sequence (as a whole) was either not executed until it parsed correctly, or was run in a transaction, and thus all modifications were rolled back.

    2. Executing multiple SQL statements in one ->do call is dependent on the database driver. I wouldn't rely on it.

    3. It depends on what the database driver implements. Ask the database vendor about the rationale.

      Thanks for your answers, Corion.  I'm using MySQL, but I don't think MySQL is preventing multiple statements, since from the Linux prompt I can echo multiple SQL commands and pipe them to a mysql command and they work.  From looking below at chacham's answer, it looks as if DBI prevents it, which is fine by me.

        I don't think DBI prevents anything. It passes the text you give it to the database to parse. If the mysql command line takes multiple statements, then the command line tool is likely parsing separate statements. Likewise, in Oracle, you can pass multiple SQL statements to sqlplus, but not to DBI, because sqlplus does some parsing of its own.

        I do see that the latest version of DBD::mysql supports multiple result sets. You might want to look into that.

        It's the mysql client library C API that introduces the limitation:

        Executes the SQL statement pointed to by the null-terminated string stmt_str. Normally, the string must consist of a single SQL statement without a terminating semicolon (;) or \g.

        Special steps must be taken to allow execution of multiple statements, and seems to be new in version 5.0.

        The Postgresql C API doesn't follow that model, but makes another interesting remark:

        Note however that the returned PGresult structure describes only the result of the last command executed from the string

        which makes it less useful to run multiple statements in a single API call.

Re: Multiple SQL statements in DBI
by mje (Deacon) on Sep 07, 2012 at 09:06 UTC

    I think others have answered your question but as a maintainer of 2 DBDs I'd strongly suggest you avoid attempting to run multiple statements like this. The support for doing that in actual drivers and databases varies massively and in nearly all cases I've seen it has led to other problems.

      Thanks mje, but ultimately I'm not really wanting to run multiple statements in one.  See my question 3 for my motivation.
Re: Multiple SQL statements in DBI
by moritz (Cardinal) on Sep 07, 2012 at 10:09 UTC
Re: Multiple SQL statements in DBI
by chacham (Curate) on Sep 07, 2012 at 10:33 UTC

    1. If the error is with setting nickname = 'three', then why doesn't nickname end up being = 'two'?

    Because the entire second batch is rejected. The error does not mean that the second statement in the second batch failed; it means that because of the second statement, the entire batch failed.

    2. Does DBI allow multiple SQL statements to be run in one invocation? If so, how can that be done?

    The DBI documentation states "Multiple SQL statements may not be combined in a single statement handle ($sth), although some databases and drivers do support this (notably Sybase and SQL Server)."

    Technically, DBI allows multiple statements to be passed in one call, though it is up to the specific driver to allow multiple statement in one batch.

    As a block in considered one statement (like multiple lines of code in curly braces), most implementations allow passing an anonymous block with multiple statements. Such as:

    BEGIN statement 1; statement 2; END

    3. Or if not, why not? Is it to help prevent SQL injection, perhaps? That's fine, as that's the reason for my test, but I'd like to see how to make it happen before I see if my methods of prevention are working.

    Why not? Because database CLI drivers generally allow exactly one statement to be processed, and DBI can't change that.

    SQL Injection? No. SQL Injection is only a concern with dynamic SQL. Multiple statements and dynamic SQL are two completely different things.

      The DBI documentation states "Multiple SQL statements may not be combined in a single statement handle ($sth), although some databases and drivers do support this (notably Sybase and SQL Server)."

      Thanks for reminding me of that. I need to change that as although it is true for MS SQL Server it is full of problems e.g., you may need to call SQLMoreResults to move from one statement to the next, it will break horribly with certain types of procedure calls and array fetching is affected too.

      Thanks very much for all that, chacham!

      I'm not sure where CLI driver is, but if it's part of MySQL (the DB I'm using), then note that from the Linux prompt I can echo multiple SQL commands and pipe them to a mysql command and they work.  Good point re the DBI documentation, though - I should have seen that.

        I'm not sure where CLI driver is,

        CLI is the Call Level Interface. when you type in mysql and get a prompt, that program is using the CLI to communicate with the database. (At least that how everyone else does it.) In a sense, you are never "in" the database, you are always making calls to it instead. But those nice people at mysql have a nifty program to make basic calls for you, behind the scenes, per se.

        When you echo multiple statement in a client program, it sends each statement separately through the CLI. That's two statements, two cursors, et al. It hides it from you, but there are two calls. When you are using DBI, you are taking control of when to call the CLI (via DBI, which does it for you), so when you have two statements, the CLI gets called only once, and it doesn't support multiple statements in the same batch.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://992255]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (15)
As of 2014-07-10 13:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (211 votes), past polls