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