[prog] Re: Placeholders (was MySQL connections from PHP files...)

[Jacinta Richardson]

Gareth Anderson wrote:
> I'm sorry I still don't get placeholders, I understand that its
> something your simply substituting and thats good for speed.

> How does placeholders help here?
> Couldn't you just do an SQL injection attack?
> What if we submitted the id as: ' OR '' = '
> Wouldn't that return all users in the database with any ID?

If we submit the id as ' OR '' = ' then due to how placeholders are implemented 
our actual statement would effectively look like:

     SELECT title, author, date ...
     FROM entries
     WHERE id='\' OR \'\' = \''

that is, the database driver will do the required quoting.  Some databases use 
double tick '' to represent a single literal quote.  By leaving it to the 
database driver, you don't have to worry about that kind of thing.

When placeholders are used the driver doesn't say to the database "execute this 
bit of SQL verbatim thanks", instead it says "you know that statement with 
placeholders I just gave you?  the first bit of data is this, and the second bit 
is this...."

Thus the database knows that you're looking for something where the
id is ' OR '' = ' rather than putting that into the SQL directly and making the 
subsequent mistake.

> Could you show me a more complicated example, like using a username/password?
> 
> $allow = "SELECT * FROM users
>                 WHERE username=? and password=?";
> 
> Would that be valid for a string we're going to perform the bind on?

That would be completely correct.  And the code would be:

               $sth = $db->prepare($allow);
               $sth->bind_param("ss", $username, $password);
               $sth->execute();

You can use as many placeholders as you need (although only for single values 
and only inside the WHERE clause).

> Sorry, I'm very confused today :(

No problem at all.  Let me know if there's anything else I can help you with.

     Jacinta

 From the Perl DBI documentation:

Placeholders and Bind Values
-----------------------------

Some drivers support placeholders and bind values. Placeholders, also called 
parameter markers, are used to indicate values in a database statement that will 
be supplied later, before the prepared statement is executed. For example, an 
application might use the following to insert a row of data into the SALES table:

   INSERT INTO sales (product_code, qty, price) VALUES (?, ?, ?)

or the following, to select the description for a product:

   SELECT description FROM products WHERE product_code = ?

The ? characters are the placeholders. The association of actual values with 
placeholders is known as binding, and the values are referred to as bind values.

Note that the ? is not enclosed in quotation marks, even when the placeholder 
represents a string. Some drivers also allow placeholders like :name and :n 
(e.g., :1, :2, and so on) in addition to ?, but their use is not portable.

With most drivers, placeholders can't be used for any element of a statement 
that would prevent the database server from validating the statement and 
creating a query execution plan for it. For example:

   "SELECT name, age FROM ?"         # wrong (will probably fail)
   "SELECT name, ?   FROM people"    # wrong (but may not 'fail')

Also, placeholders can only represent single scalar values. For example, the 
following statement won't work as expected for more than one value:

   "SELECT name, age FROM people WHERE name IN (?)"    # wrong
   "SELECT name, age FROM people WHERE name IN (?,?)"  # two names

When using placeholders with the SQL LIKE qualifier, you must remember that the 
placeholder substitutes for the whole string. So you should use "... LIKE ? ..." 
and include any wildcard characters in the value that you bind to the placeholder.


-- 
    ("`-''-/").___..--''"`-._          |  Jacinta Richardson         |
     `6_ 6  )   `-.  (     ).`-.__.`)  |  Perl Training Australia    |
     (_Y_.)'  ._   )  `._ `. ``-..-'   |      +61 3 9354 6001        |
   _..`--'_..-_/  /--'_.' ,'           | contact at perltraining.com.au |
  (il),-''  (li),'  ((!.-'             |   www.perltraining.com.au   |