Pete Freitag Pete Freitag

Prepared Statements in PHP and MySQL

Published on May 16, 2005
By Pete Freitag
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.



php mysql prepared statements cfqueryparam mysqli

Prepared Statements in PHP and MySQL was first published on May 16, 2005.

If you like reading about php, mysql, prepared statements, cfqueryparam, or mysqli then you might also like:

Discuss / Follow me on Twitter ↯

Comments

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]')";
by sreekandan.N on 09/13/2006 at 4:59:41 AM UTC
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
by J Selvakumar Micheal on 12/06/2007 at 5:06:14 AM UTC
Great, I was looking for some thing like this, will surely link to this on my blog http://www.php-trivandrum.org
by php-trivandrum.org on 02/08/2008 at 6:43:53 AM UTC
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.
by Melissa on 05/28/2008 at 4:18:18 AM UTC