Prepared Statements in PHP and MySQL

databases

I'm working on a web security presentation, and I was curious to know if PHP supported prepared statements. It looks like as of PHP 5 they do support it with the new mysqli object (mysqli replaces the mysql class with support for mysql 4.x features)

Here's how you do a prepared statement with php 5 and mysql (error checking is omitted):

$db_connection = new mysqli("localhost", "user", "pass", "db");
$statement = $db_connection->prepare("SELECT thing FROM stuff WHERE id = ?");
$statement->bind_param("i", $id);
$statement->execute();
...

The first argument of bind_param is the type, in this case I used i for integer - you can also use s for string, d for double, and b for blob.

The variables in your query are represented with the ? question marks, just like with JDBC. This makes maintenance kind of a pain, it makes you appreciate CFML's prepared statement implementation with cfqueryparam.

You can also use PEAR:DB to run prepared statements in PHP, since it is a database abstraction layer, it is probably a good way to go.

MySQL supports prepared statements in version 4.1 and above.



Related Entries

11 people found this page useful, what do you think?

Trackbacks

Trackback Address: 356/2075D8C266D2DB8D5FD366E7F7EBCFA1

Comments

On 09/13/2006 at 6:59:41 AM EDT sreekandan.N wrote:
1
pls help me . i had created a unique id in php and its not inserted in the database. $prefix = 'W'; // a universal prefix prefix $my_random_id = $prefix; $my_random_id .= chr(rand(65,90)); $my_random_id .= time(); $my_random_id .= $prefix; i had used this coding and msql query as print $my_random_id; $qurey="INSERT INTO FeedBackDetails (Name,Age,DateOfBirth,Gender,Adress,PhoneNo,MobileNo,Email,Profession,Comments,CustomerId) VALUES ('$_POST[name]','$_POST[age]','$_POST[date]','$_POST[gender]','$_POST[adress]','$_POST[phone]','$_POST[mobile]','$_POST[mail]','$_POST[profession]','$_POST[comments]','$_POST[my_random_id]')";

On 12/06/2007 at 7:06:14 AM EST J Selvakumar Micheal wrote:
2

Hi, i need an urgent help, In mysql Without using the prepare statement how to assign dynamicaly a table name in a procedure. the sample coding is given bellow.

create procedure table_name(x varchar(100)) begin select * from x; end;|

call table_name('books');

It shows an error message table x doesnot existt

Any one can help me how to solve this problem

On 12/06/2007 at 7:06:41 AM EST J Selvakumar Micheal wrote:
3
Hi, i need an urgent help, In mysql Without using the prepare statement how to assign dynamicaly a table name in a procedure. the sample coding is given bellow.

create procedure table_name(x varchar(100)) begin select * from x; end;|

call table_name('books');

It shows an error message table x doesnot existt

Any one can help me how to solve this problem

On 02/08/2008 at 8:43:53 AM EST php-trivandrum.org wrote:
4
Great, I was looking for some thing like this, will surely link to this on my blog http://www.php-trivandrum.org

On 05/28/2008 at 6:18:18 AM EDT Melissa wrote:
5
Are you a big fan of movies and all the new releases on the big screen? Do you like to watch all the latest movies as soon as they are released? If the answer is yes, and you not only love to watch movies but you also like to get loads of other movie related products as well then there is a web site that is perfectly suited to you. The web site that you should consider taking a look at is called. The Films gives its visitors the chance to down load many of the latest movie releases as well as offering loads of news about all that is going on with in the movie industry and the actors in and around Hollywood. From this web sites well designed menu system you can also access movie sound tracks, and down load wall papers of you favorite movies and movie stars.

On 03/19/2009 at 5:42:56 AM EDT innocent wrote:
6
someone help everytime when i use the prepared statement it give me the error message that says, i am trying to bind with the non object parameter

On 11/03/2010 at 4:33:15 AM EDT public auctions Highland Park wrote:
7
Thanks For This Blog, was added to my bookmarks.

On 06/16/2011 at 4:19:19 PM EDT engendJoymn wrote:
8
ipage hosting information

On 06/23/2011 at 12:27:02 PM EDT PaxPerVargere wrote:
9
cheap vps hosting - UK

On 07/14/2011 at 11:52:31 AM EDT justhostweb wrote:
10
justhost hosting cheap cpanel hosting http://technologycanada.com/justhost-hosting/

On 07/17/2011 at 8:04:42 PM EDT hostgator coupon wrote:
11
hostgator review , top webhosting http://technologycanada.com/hostgator-hosting/

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?