MySQL में Prepared Statements

Prepared Statement क्या है?

Prepared statement SQL template को execution data values से अलग करता है। Bound value के अंदर quotes/punctuation executable SQL नहीं बनते।

SELECT order_id,customer_name
FROM orders_prepared_lab
WHERE status=? AND total_amount>=?;

• Security: correctly bound values SQL injection resist करते हैं।
• Reuse: same template repeated हो तो parsing/data overhead घट सकता है।

Orders Lab

DROP TABLE IF EXISTS orders_prepared_lab;
CREATE TABLE orders_prepared_lab (
  order_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_name VARCHAR(100) NOT NULL,
  status VARCHAR(20) NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  order_date DATE NOT NULL,
  INDEX ix_orders_status_amount(status,total_amount)
) ENGINE=InnoDB;
INSERT INTO orders_prepared_lab
  (customer_name,status,total_amount,order_date)
VALUES
('Aman','PAID',1200.00,'2026-08-01'),
('Riya','PENDING',500.00,'2026-08-05'),
('Kabir','PAID',750.00,'2026-08-03'),
('Meera','CANCELLED',300.00,'2026-08-04'),
('Sara','PAID',1500.00,'2026-08-10');

Composite index status equality और amount range support करता है। Prepared statement indexing/optimization replace नहीं करता।

PREPARE, EXECUTE और DEALLOCATE

PREPARE order_search FROM
 'SELECT order_id,customer_name,total_amount
  FROM orders_prepared_lab
  WHERE status=? AND total_amount>=?
  ORDER BY order_id';
SET @status='PAID';
SET @minimum=1000.00;
EXECUTE order_search USING @status,@minimum;
SET @minimum=700.00;
EXECUTE order_search USING @status,@minimum;
DEALLOCATE PREPARE order_search;

EXECUTE USING में exact marker count के user variables चाहिए। Statement current session का है; काम पूरा हो तो deallocate करें।

Secure PHP PDO Example

$pdo = new PDO(
 'mysql:host=localhost;dbname=school;charset=utf8mb4',
 $dbUser,$dbPassword,[
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
  PDO::ATTR_EMULATE_PREPARES => false,
 ]
);
$stmt=$pdo->prepare(
 'SELECT order_id,customer_name,total_amount
  FROM orders_prepared_lab
  WHERE status=:status AND total_amount>=:minimum
  ORDER BY order_id'
);
$stmt->execute([
 ':status'=>'PAID', ':minimum'=>1000.00
]);
$rows=$stmt->fetchAll();

Credentials protected environment configuration में रखें। Actual driver/server पर native prepares test करें। Repeated INSERT में prepare once, execute many times करें।

Types, LIKE, LIMIT और IN Lists

$stmt=$pdo->prepare(
 'SELECT customer_name FROM orders_prepared_lab
  WHERE customer_name LIKE :pattern'
);
$stmt->execute([':pattern'=>'%am%']);

$stmt=$pdo->prepare(
 'SELECT order_id FROM orders_prepared_lab
  ORDER BY order_id LIMIT :row_count'
);
$stmt->bindValue(':row_count',10,PDO::PARAM_INT);

One marker multiple IN values represent नहीं करता। One marker per value बनाएँ:

$statuses=['PAID','PENDING'];
$marks=implode(',',array_fill(0,count($statuses),'?'));
$sql="SELECT order_id FROM orders_prepared_lab
      WHERE status IN ($marks)";
$stmt=$pdo->prepare($sql);
$stmt->execute($statuses);

Dynamic Identifiers

Parameters identifiers/keywords नहीं बन सकते। Strict mapping use करें:

$allowedSort=[
 'date'=>'order_date',
 'amount'=>'total_amount',
 'customer'=>'customer_name'
];
$allowedDirection=['asc'=>'ASC','desc'=>'DESC'];
$sort=$allowedSort[$_GET['sort']??'date']??'order_date';
$direction=$allowedDirection[
 strtolower($_GET['dir']??'desc')
]??'DESC';
$sql="SELECT order_id,customer_name,total_amount
      FROM orders_prepared_lab
      WHERE status=:status ORDER BY `$sort` $direction";

SQL structure में केवल hard-coded mapping values आएँ। Arbitrary identifier से characters हटाना authorization नहीं है।

Transactions, Errors और Lifecycle

try {
 $pdo->beginTransaction();
 $update=$pdo->prepare(
  'UPDATE orders_prepared_lab
   SET status=:status WHERE order_id=:id'
 );
 $update->execute([':status'=>'PAID',':id'=>2]);
 if($update->rowCount()!==1){
  throw new RuntimeException('Order was not updated');
 }
 $pdo->commit();
} catch(Throwable $e) {
 if($pdo->inTransaction()) $pdo->rollBack();
 throw $e;
}

• Prepare transaction start नहीं करता।
• Rollback deliberate रखें; SQL/credentials user error में न दिखाएँ।
• Server SQL PREPARE multi-statements support नहीं करता।
• Long sessions में max_prepared_stmt_count monitor करें।

Security और Review Checklist

1. हर external data value bind करें।
2. Raw request SQL में concatenate न करें।
3. Identifiers/operators allowlist करें।
4. IN में हर item का marker।
5. LIKE wildcard/escaping define करें।
6. Type, range और authorization separately validate करें।
7. Least-privileged DB account रखें।
8. Exceptions enable, DB errors hide करें।
9. Quotes, Unicode, NULL, boundaries test करें।
10. Query plan/index measure करें।

SQL injection, optimization, transactions और privileges पढ़ें।

Official संदर्भ

• MySQL 8.4: Prepared Statements
• MySQL 8.4: PREPARE
• PHP Manual: PDO::prepare

Marker rules, lifecycle, SQL syntax और PDO limits official MySQL/PHP docs से verify किए गए हैं।