Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Re^2: Getting mysql data into gd

by shanta (Novice)
on Sep 03, 2017 at 12:53 UTC ( #1198622=note: print w/replies, xml ) Need Help??


in reply to Re: Getting mysql data into gd
in thread Getting mysql data into gd

I am a hacker using Perl to get a job done. Entirely self taught. Perl Arrays and esoteric place holders have always mystified me!

Heck I can do a TTML quires and populate a table or whatever but Don't know how to access GD from TTML or I would rather than drop into Perl.

I can take code and make it do what I want. To a point at least.

Of course I am here to learn so can you demystify it for me?

Replies are listed 'Best First'.
Re^3: Getting mysql data into gd
by roboticus (Chancellor) on Sep 03, 2017 at 15:18 UTC

    shanta:

    Placeholders are actually very easy, and prevent a lot of problems. To use placeholders, you just replace variable references with question marks, then provide the values as arguments to execute.

    So suppose you have something like this:

    my $ST = $DB->prepare("select foo, bar from my_table " ."where col1 < '$abc' or (col1 > '$def' and col3 +< $fgh)"); $ST->execute();

    As mentioned, you change the variables to question marks and provide the variables to the execute statement, like this:

    my $ST = $DB->prepare("select foo, bar from table where col1 < ? or (c +ol1 > ? and col3 < ?)"); $ST->execute($abc, $def, $fgh);

    Note that we also removed the quotes on the first two variable references, because the database already knows that column1 is a string.

    Using placeholders is nice, because you can use the same statement with different values for different executes which will let the database compile the statement once and re-use the same execution plan for successive runs. (Well, at least *some* databases will take advantage of that.)

    Placeholders are even more important because they take advantage of any funky special cases for quoting the variable values, and thereby help you resist SQL injection attacks. For example, suppose $abc in the example above contains the value "'; drop table my_table; --". Then your original statement would be passed to the database as the following (with a few newlines thrown in for readability):

    select foo, bar from my_table where col1 < ''; drop table my_table ; --' or (col1 > 'def_val' and col3 < fgh_val)

    That's a recipe for disaster. So make the effort to learn placeholders a bit better (shouldn't take long), and you'll never go back to doing it the hard way.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

      thanks for the great response

      I can see the drift of it. How would this prevent drop table my_table example? It could be in the variable being sent anyway.

      can I send?

      $ST->execute(text, text2, moretext);

      Thanks for the help

        shanta:

        DBI has all the SQL quoting rules handled, so using placeholders would turn it into:

        select foo, bar from my_table where col < '''drop table my_table; --' or (col > 'def_val' and col3 < fgh_val)

        Here, the drop table command isn't exposed as an SQL statement, it's just a column value checked in the where clause. If you were really careful all the time and made sure you handled all the quoting and comments properly, you could ignore placeholders and not run into trouble. But I don't know anyone careful enough to *always* get it right. Instead DBI does it for you, and you don't need to remember the rules for whichever database you use.

        Some database back ends understand placeholders and can give you a performance boost when you use them, so using placeholders gives you the chance to squeeze a little more performance out of your database for the ones that understand it (such as Sybase, MSSQL, Oracle, Pg).

        For example, suppose you wanted to do something like this:

        insert into my_table select ? as foo, ? as bar, c.id as product_id, d.price as product_pric +e from my_stuff c where c.name=? join friend_prices d where d.name=? and c.class=d.class

        When the SQL back-end parses this, it writes a little program to do the table joins and lookups for the insert. If you just substitute the values into the SQL and prepare then execute it each time, it has to repeat all the work to generate the query plan each time. But if you use placeholders, it only needs to do generate the query plan once, because you can re-use the statement:

        my $ST = db->prepare(q{ insert into my_table select ? as foo, ? as bar, c.id as product_id, d.price as product_ +price from my_stuff c where c.name=? join friend_prices d where d.name=? and c.class=d.class }); $ST->execute(123, 456, 'Mouse', 'Joe'); $ST->execute(149, 357, 'Mousepad', 'Joe');

        ...roboticus

        When your only tool is a hammer, all problems look like your thumb.

Re^3: Getting mysql data into gd (SQL placeholders)
by hippo (Chancellor) on Sep 04, 2017 at 04:19 UTC

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (5)
As of 2021-01-27 16:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?