How to use stored procedure in PHP

gamzatti
Vote 0 Votes

How to use stored procedure in PHP


- Create procedure
delimiter //
CREATE PROCEDURE selectOrders(IN user varchar(255), IN keyword varchar(255))
BEGIN
  SELECT * from orders where username=user and ordername =keyword;
END;

- Check procedure
show procedure status;
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db       | Name         | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| shopping | selectOrders | PROCEDURE | root@localhost | 2017-04-14 17:37:18 | 2017-04-14 17:37:18 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+----------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
show create procedure selectOrders;

---------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure    | sql_mode                                   | Create Procedure                                                                                                                                                                       | character_set_client | collation_connection | Database Collation |
+--------------+--------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| selectOrders | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `selectOrders`(IN user varchar(255), IN keyword varchar(255))
BEGIN
  SELECT * from orders where username=user and ordername =keyword;
END | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+--------------+--------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)
 call selectOrders('User1','Buy books');
+----+----------+-----------+--------+---------------------+---------------------+--------+--------+
| id | username | ordername | amount | create_time         | update_time         | extra1 | extra2 |
+----+----------+-----------+--------+---------------------+---------------------+--------+--------+
|  1 | User1    | Buy books |   5000 | 2017-04-07 14:07:30 | 2017-04-07 14:07:30 | NULL   | NULL   |
+----+----------+-----------+--------+---------------------+---------------------+--------+--------+

- Call procedure from PHP (PDO)
<?php
$ds = "mysql:host=localhost;dbname=shopping";
$user = "root";
$pass = "root";
$dbh = new PDO($ds, $user, $pass);
$stmt = $dbh->prepare("call selectOrders(?,?)");
 $stmt->bindParam(1,$username,PDO::PARAM_STR);
 $stmt->bindParam(2,$keyword,PDO::PARAM_STR);
$stmt->execute();
$query = $stmt->queryString;
while($row = $stmt->fetch()){
        print $row["username"];
        print $row["ordername"];
        print $row["amount"];
}
?>

Leave a comment

About this Entry

This page contains a single entry by gamzatti published on April 14, 2017 10:38 PM.

How to implements webshell using JSP was the previous entry in this blog.

Vulnerable sample code -OS command injection- is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.