PHP MYSQL

A QUICK EXAMPLE OF SQL INJECTION & PREVENTION

id | Product ID, primary key. name | Product name. status | 1 for active, 0 for hidden.

PRODUCTS TABLE

01

<form method="post">    <input type="text" name="search"/>    <input type="submit" value="GO"/> </form>

HTML SEARCH FORM

02

MYSQL INEJECTION (A)

03

CONNECT TO DATABASE $pdo = new PDO("mysql:host=HOST; charset=utf8dbname=NAME", USER, PASSWORD);

MYSQL INEJECTION (B)

04

BAD INJECTION PRONE QUERY $sql = "SELECT * FROM `products` WHERE `status`=1 AND `name` LIKE \"%".$_POST["search"]."%\"";

PROCEED SEARCH $stmt = $pdo->prepare($sql); $stmt->execute(); $results = $stmt->fetchAll();

MYSQL INEJECTION (C)

05

ENTER THIS SEARCH TERM " OR 1=1 OR `name` LIKE "

SEARCH QUERY TURNS INTO SELECT * FROM `products` WHERE `status`=1 AND `name` LIKE "%" OR 1=1 OR `name` LIKE "%"

* THIS WILL "SHOW ALL PRODUCTS", NOT "SEARCH ACTIVE PRODUCTS ONLY".

INEJECTION PRVENTION

06

DO NOT FEED SEARCH TERM INTO SQL STRING DIRECTLY $sql = "SELECT * FROM `products` WHERE `status`=1 AND `name` LIKE ?"; $stmt = $pdo->prepare($sql);

PUT INTO EXECUTE() INSTEAD $stmt->execute   (["%".$_POST["search"]."%"]); $results = $stmt->fetchAll();