PHP MySQLi Prepared Statements Tutorial to Prevent SQL Injection

Last updated date: 2025-04-25 08:55:44

Add your website completely free of charge

Meta tags

Meta Tag Content
viewport width=device-width, initial-scale=1
description Before I start, if you'd like to see an even easier way to use MySQLi prepared statements, check out my wrapper class. Also, here's a great resource to learn PDO prepared statements, which is the better choice for beginners and most people in general. A hack attempt has recently been discovered, and it appears they are trying to take down the entire database. An impromptu staff meeting has bee...
theme-color #ffffff       
Website Page URL https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection

Heading tags

h1 tag

We found around "1" h1 tags which are found in this page url and are available in the table below.

S.no h1 tag content
1 PHP MySQLi Prepared Statements Tutorial to Prevent SQL Injection

h2 tag

We found around "10" h2 tags which are found in this page url and are available in the table below.

S.no h2 tag content
1 Introduction
2 How SQL Injection Works
3 How MySQLi Prepared Statements Work
4 Insert, Update and Delete
5 Select
6 Like
7 Where In Array
8 Multiple Prepared Statements in Transactions
9 Error Handling
10 Some Extras

h3 tag

We found around "27" h3 tags which are found in this page url and are available in the table below.

S.no h3 tag content
1 Table of Contents
2 Creating a New MySQLi Connection
3 Insert
4 Update
5 Delete
6 Get Number of Affected Rows
7 Get Rows Matched
8 Get Latest Primary Key Inserted
9 Check if Duplicate Entry
10 get_result()
11 bind_result()
12 Fetch Associative Array
13 Fetch Numeric Array
14 Fetch Single Row
15 Fetch Array of Objects
16 Conclusion
17 With Other Placeholders
18 Reuse Same Template, Different Values
19 Fatal error: Uncaught Error: Call to a member function bind_param() on boolean
20 Exception Handling
21 Custom Exception Handler
22 Gotcha with Exception Handling
23 Do I Need $stmt->close()?
24 Classes: mysqli vs. mysqli_stmt vs. mysqli_result
25 So Using Prepared Statements Means I'm Safe From Attackers?
26 Series
27 Author - Daniel Marcus

h4 tag

We found around "1" h4 tags which are found in this page url and are available in the table below.

S.no h4 tag content
1 Table of Contents

h5 tag

Unfortunately we were not able to find any h3 tag in the URL of this page.

h6 tag

Unfortunately we were not able to find any h3 tag in the URL of this page.

HTML Formatting Elements - Important text (strong/bold) tags

S.no Tag content
1 Without quotes, strings are still equally susceptible to SQL injection
2 don't use this for table/column names or SQL keywords
3 there's absolutely no good reason to be using real_escape_string() over prepared statements.
4 Please don't ever report errors directly on your site in production.
5 display_errors = Off
6 log_errors = On
7 you need to ensure that it's in a try/catch block and you specifically print in your error log $e->getMessage(), not $e, which still contains your sensitive information
8 -1
9 Greater than 0
10 1062
11 23000
12 One Row
13 $result->fetch_assoc()
14 $result->fetch_row()
15 $result->fetch_object()
16 All
17 $result->fetch_all(MYSQLI_ASSOC)
18 $result->fetch_all(MYSQLI_NUM)
19 bind_result()
20 get_result()
21 TLDR;
22 mysqli::$affected_rows
23 mysqli_stmt::$affected_rows
24 mysqli_result::$num_rows
25 mysqli_stmt::$num_rows
26 mysqli::$insert_id
27 mysqli_stmt::$insert_id

HTML Formatting Elements - Important text (i) tags

S.no Tag content

HTML Formatting Elements - Underline text (u) tags

S.no Tag content

HTML Formatting Elements - Code tags

S.no Tag content
1 
$name = $_POST['name'];
$mysqli->query("SELECT * FROM myTable WHERE name='$name'");
2 
' OR '1'='1
3 
1=1
4 
DELETE
5 
SELECT * FROM myTable WHERE name='' OR '1'='1'
6 
$name = $mysqli->real_escape_string($_POST['name']);
$mysqli->query("SELECT * FROM myTable WHERE name='$name'");
7 
addcslashes($escaped, '%_')
8 
mysqli::real_escape_string
9 
(int)$mysqli->real_escape_string($_POST['name'])
10 
(int)$_POST['name']
11 
name
12 
(int)$var
13 
default_charset = "utf-8"
14 
$mysqli->set_charset('utf8mb4')
15 
$mysqli->real_escape_string()
16 
real_escape_string()
17 
$stmt = $mysqli->prepare("SELECT * FROM myTable WHERE name = ? AND age = ?");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
//fetching result would go here, but will be covered later
$stmt->close();
18 
myTable
19 
age
20 
?
21 
bind_param()
22 
s
23 
i
24 
$stmt->execute()
25 
mysqli_connect.php
26 
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)
27 
$mysqli = new mysqli("localhost", "username", "password", "databaseName");
if($mysqli->connect_error) {
  exit('Error connecting to database'); //Should be a message a typical user could understand in production
}
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli->set_charset("utf8mb4");
28 
$mysqli->connect_error()
29 
exit()
30 
die()
31 
exit('Something weird happened')
32 
utf-8
33 
utf8mb4
34 
display_errors = Off
35 
log_errors = On
36 
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
  $mysqli = new mysqli("localhost", "username", "password", "databaseName");
  $mysqli->set_charset("utf8mb4");
} catch(Exception $e) {
  error_log($e->getMessage());
  exit('Error connecting to database'); //Should be a message a typical user could understand
}
37 
try/catch
38 
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
set_exception_handler(function($e) {
  error_log($e->getMessage());
  exit('Error connecting to database'); //Should be a message a typical user could understand
});
$mysqli = new mysqli("localhost", "username", "password", "databaseName");
$mysqli->set_charset("utf8mb4");
39 
mysqli_report()
40 
$mysqli->connect_error
41 
new mysqli()
42 
$e->getMessage()
43 
$e
44 
set_exception_handler()
45 
$stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
$stmt->close();
46 
$stmt = $mysqli->prepare("UPDATE myTable SET name = ? WHERE id = ?");
$stmt->bind_param("si", $_POST['name'], $_SESSION['id']);
$stmt->execute();
$stmt->close();
47 
$stmt = $mysqli->prepare("DELETE FROM myTable WHERE id = ?");
$stmt->bind_param("i", $_SESSION['id']);
$stmt->execute();
$stmt->close();
48 
$stmt = $mysqli->prepare("UPDATE myTable SET name = ?");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
if($stmt->affected_rows === 0) exit('No rows updated');
$stmt->close();
49 
mysqli::$affected_rows
50 
execute()
51 
UPDATE
52 
WHERE
53 
mysqli_result::$num_rows
54 
SELECT
55 
$mysqli->affectedRows
56 
$stmt = $mysqli->prepare("UPDATE myTable SET name = ?");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
$stmt->close();
echo $mysqli->info;
57 
Rows matched: 1 Changed: 0 Warnings: 0
58 
mysqli->info
59 
preg_match_all('/(\S[^:]+): (\d+)/', $mysqli->info, $matches); 
$infoArr = array_combine ($matches[1], $matches[2]);
var_export($infoArr);
60 
['Rows matched' => '1', 'Changed' => '0', 'Warnings' => '0']
61 
$stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
$stmt->bind_param("si", $_POST['name'], $_POST['age']);
$stmt->execute();
echo $mysqli->insert_id;
$stmt->close();
62 
$mysqli->errno
63 
$e->getCode()
64 
$mysqli->sqlstate
65 
try {
  $stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
  $stmt->bind_param("si", $_POST['name'], $_POST['age']);
  $stmt->execute();
  $stmt->close();
} catch(Exception $e) {
  if($mysqli->errno === 1062) echo 'Duplicate entry';
}
66 
ALTER TABLE myTable ADD CONSTRAINT unique_person UNIQUE (name, age)
67 
get_result()
68 
bind_result()
69 
$result = get_result()
70 
$result = $mysqli->query()
71 
$result->fetch_assoc()
72 
$result->fetch_row()
73 
$result->fetch_object()
74 
$result->fetch_all(MYSQLI_ASSOC)
75 
$result->fetch_all(MYSQLI_NUM)
76 
$stmt = $mysqli->prepare("SELECT * FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
if($result->num_rows === 0) exit('No rows');
while($row = $result->fetch_assoc()) {
  $ids[] = $row['id'];
  $names[] = $row['name'];
  $ages[] = $row['age'];
}
var_export($ages);
$stmt->close();
77 
[22, 18, 19, 27, 36, 7]
78 
*
79 
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$stmt->store_result();
if($stmt->num_rows === 0) exit('No rows');
$stmt->bind_result($idRow, $nameRow, $ageRow); 
while($stmt->fetch()) {
  $ids[] = $idRow;
  $names[] = $nameRow;
  $ages[] = $ageRow;
}
var_export($ids);
$stmt->close();
80 
[106, 221, 3, 55, 583, 72]
81 
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
82 
fetch_assoc()
83 
$arr = [];
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_assoc()) {
  $arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
84 
[
  ['id' => 27, 'name' => 'Jessica', 'age' => 27], 
  ['id' => 432, 'name' => 'Jimmy', 'age' => 19]
]
85 
mysqli_result->fetch_all(MYSQLI_NUM)
86 
mysqli_result->fetch_row()
87 
$stmt = $mysqli->prepare("SELECT location, favorite_color, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_NUM);
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
88 
$arr = [];
$stmt = $mysqli->prepare("SELECT location, favorite_color, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_row()) {
  $arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
89 
[
  ['Boston', 'green', 28], 
  ['Seattle', 'blue', 49],
  ['Atlanta', 'pink', 24]
]
90 
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$stmt->store_result();
if($stmt->num_rows === 0) exit('No rows');
$stmt->bind_result($id, $name, $age);
$stmt->fetch();
echo $name; //Output: 'Ryan'
$stmt->close();
91 
$name
92 
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
$stmt->bind_param("s", $_POST['name']);
$stmt->execute();
$arr = $stmt->get_result()->fetch_assoc();
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
93 
$arr['id']
94 
['id' => 36, 'name' => 'Kevin', 'age' => 39]
95 
$arr[0]->age
96 
$arr = []
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE id = ?");
$stmt->bind_param("s", $_SESSION['id']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_object()) {
  $arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
97 
[
  stdClass Object ['id' => 27, 'name' => 'Jessica', 'age' => 27], 
  stdClass Object ['id' => 432, 'name' => 'Jimmy', 'age' => 19]
]
98 
PDO::FETCH_PROPS_LATE
99 
class myClass {}
$arr = [];
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE id = ?");
$stmt->bind_param("s", $_SESSION['id']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_object('myClass')) {
  $arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
100 
class myClass {
  private $id;
  public function __construct($id = 0) {
    if($this->id === 0) $this->id = $id;
  }
}
$arr = [];
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE id = ?");
$stmt->bind_param("s", $_SESSION['id']);
$stmt->execute();
$result = $stmt->get_result();
while($row = $result->fetch_object('myClass')) {
  $arr[] = $row;
}
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
101 
fetch_object('myClass')
102 
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE Name LIKE %?%");
103 
$search = "%{$_POST['search']}%";
$stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name LIKE ?"); 
$stmt->bind_param("s", $search);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$arr) exit('No rows');
var_export($arr);
$stmt->close();
104 
WHERE IN
105 
call_user_func_array()
106 
$inArr = [12, 23, 44];
$clause = implode(',', array_fill(0, count($inArr), '?')); //create 3 question marks
$types = str_repeat('i', count($inArr)); //create 3 ints for bind_param
$stmt = $mysqli->prepare("SELECT id, name FROM myTable WHERE id IN ($clause)");
$stmt->bind_param($types, ...$inArr);
$stmt->execute();
$resArr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$resArr) exit('No rows');
var_export($resArr);
$stmt->close();
107 
$inArr = [12, 23, 44];
$clause = implode(',', array_fill(0, count($inArr), '?')); //create 3 question marks
$types = str_repeat('i', count($inArr)); //create 3 ints for bind_param
$types .= 'i'; //add 1 more int type
$fullArr = array_merge($inArr, [26]); //merge WHERE IN array with other value(s)
$stmt = $mysqli->prepare("SELECT id, name FROM myTable WHERE id IN ($clause) AND age > ?");
$stmt->bind_param($types, ...$fullArr); //4 placeholders to bind
$stmt->execute();
$resArr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$resArr) exit('No rows');
var_export($resArr);
$stmt->close();
108 
try {
  $mysqli->autocommit(FALSE); //turn on transactions
  $stmt1 = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
  $stmt2 = $mysqli->prepare("UPDATE myTable SET name = ? WHERE id = ?");
  $stmt1->bind_param("si", $_POST['name'], $_POST['age']);
  $stmt2->bind_param("si", $_POST['name'], $_SESSION['id']);
  $stmt1->execute();
  $stmt2->execute();
  $stmt1->close();
  $stmt2->close();
  $mysqli->autocommit(TRUE); //turn off transactions + commit queued queries
} catch(Exception $e) {
  $mysqli->rollback(); //remove all queries from queue if error (undo)
  throw $e;
}
109 
try {
  $mysqli->autocommit(FALSE); //turn on transactions
  $stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
  $stmt->bind_param("si", $name, $age);
  $name = 'John';
  $age = 21;
  $stmt->execute();  
  $name = 'Rick';
  $age = 24;
  $stmt->execute();
  $stmt->close();
  $mysqli->autocommit(TRUE); //turn off transactions + commit queued queries
} catch(Exception $e) {
  $mysqli->rollback(); //remove all queries from queue if error (undo)
  throw $e;
}
110 
$mysqli->error
111 
try {
  $stmt = $mysqli->prepare("DELETE FROM myTable WHERE id = ?");
  $stmt->bind_param("i", $_SESSION['id']);
  $stmt->execute();
  $stmt->close();

  $stmt = $mysqli->prepare("SELECT id, name, age FROM myTable WHERE name = ?");
  $stmt->bind_param("s", $_POST['name']);
  $stmt->execute();
  $arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
  $stmt->close();

  try {
    $mysqli->autocommit(FALSE); //turn on transactions
    $stmt = $mysqli->prepare("INSERT INTO myTable (name, age) VALUES (?, ?)");
    $stmt->bind_param("si", $name, $age);
    $name = 'John';
    $age = 21;
    $stmt->execute();  
    $name = 'Rick';
    $age = 24;
    $stmt->execute();
    $stmt->close();
    $mysqli->autocommit(TRUE); //turn off transactions + commit queued queries
  } catch(Exception $e) {
    $mysqli->rollback(); //remove all queries from queue if error (undo)
    throw $e;
  }  
} catch (Exception $e) {
  error_log($e);
  exit('Error message for user to understand');
}
112 
set_exception_handler(function($e) {
  error_log($e);
  exit('Error deleting');
});
$stmt = $mysqli->prepare("DELETE FROM myTable WHERE id = ?");
$stmt->bind_param("i", $_SESSION['id']);
$stmt->execute();
$stmt->close();
113 
set_error_handler(function($errno, $errstr, $errfile, $errline) {
  throw new Exception("$errstr on line $errline in file $errfile");
});
114 
$mysqli->close()
115 
$stmt->close()
116 
$result->free()
117 
$stmt->free()
118 
$stmt
119 
$stmt2
120 
mysqli_stmt::$affected_rows
121 
mysqli_stmt
122 
mysqli_stmt::$num_rows
123 
$result->num_rows
124 
$stmt->num_rows
125 
mysqli::$insert_id
126 
mysqli_stmt::$insert_id
127 
mysqli
128 
$mysqli->insert_id
129 
$stmt->insert_id

The Anchor element (a) tags

S.no Anchor tag Content
1 Portfolio
2 Services
3 Blog
4 Referral Cash
5 Contact
6 Introduction
7 How SQL Injection Works
8 How MySQLi Prepared Statements Work
9 Insert, Update and Delete
10 Select
11 Like
12 Where In Array
13 Multiple Prepared Statements in Transactions
14 Error Handling
15 Some Extras
16 MySQL
17 MySQLi
18 PHP
19 Security
20 Table of Contents
21 my wrapper class
22 PDO prepared statements
23 this site
24 this explanation
25 as stated here
26 edge cases to break
27 bitwise operator
28 mysqli_sql_exception class
29 as noted here
30 set_exception_handler()
31 restore_exception_handler()
32 helpful commenter
33 other query types
34 list of error messages
35 the entire mysqli_result class
36 a clever solution
37 this comment
38 a "bug"
39 splat operator
40 create a new connection
41 As stated earlier
42 the result
43 the parameterized query
44 mysqli::$affected_rows
45 mysqli_stmt::$affected_rows
46 mysqli_result::$num_rows
47 mysqli_stmt::$num_rows
48 mysqli::$insert_id
49 mysqli_stmt::$insert_id
50 note
51 filter_var()
52 htmlspecialchars()
53 1PHP MySQLi Prepared Statements Tutorial to Prevent SQL InjectionNov 8, 2017
54 2PHP PDO Prepared Statements Tutorial to Prevent SQL InjectionNov 26, 2017
55 3PDO vs. MySQLi: The Battle of PHP Database APIsJun 8, 2018

Contact Us

If you have any inquiries or feedback, please don't hesitate to reach out to us at [email protected]. We will respond to your request as soon as possible. Thank you very much for your interest!

Quick Links

Country profiles