From: Fagyal Csongor
Date: November 11 2004 6:09pm
Subject: Re: Prepared statement for MySQL 4.1
List-Archive: http://lists.mysql.com/mysql/175684
Message-Id: <4193AAEB.1080304@conceptonline.hu>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Scott,

>I've read the article about 'prepared statement' found in MySQL 4.1, and am
>not sure if I understood what 'prepared statement' does and how can it
>benefit us.  Can anyone elaborate on what 'prepared statement' could do with
>examples where possible?
>
>  
>
In the simplest case, consider this:

You have an application (say a webpage), which receives user input (say 
a submitted form) which contain an e-mail address that you want to 
insert into a database. Let's say you have this form field in a variable 
called $email:

Inserting:
$dbh->do("INSERT INTO mytable (email) VALUES ('$email')");

Now if you do not verify what is in $email, and the user passes you this 
: notavalidemail@'adress
you can run into trouble, as the above statement will parse to
"INSERT INTO mytable (email) VALUES ('notavalidemail@'adress)"
which is not good, it produces an SQL error because of the "extra" ' 
character. You can run into more serious trouble if you get something 
more SQL-like from your form, say "DELETE FROM mytable" or something 
like that ;-)

Using placeholders take care of quoting, that is, the SQL statement will 
always be valid, at the data will no longer be part of the query. This:
$dbh->do("INSERT INTO mytable (email) VALUES (?)", undef, $email);
...will insert $email into the table without a runtime error (well, it 
is probably not what you want, as you will have an invalid e-mail 
address, but it is still better compared to letting others execute SQL 
commands on your machine...)

This thing by itself should be enough for anyone NOT to use 
"non-prepared" statements (like many badly written PHP scripts do). But 
there are more things... well, read the article once again, or wait for 
another e-mail on speed benefits :-)

Regards,
- Csongor