void Tricks Logo
void Tricks Logo

PHP MySQL Prepared Statements Class File

Include this simple class file in your PHP projects to implement MySQL prepared statements to prevent SQL injection attacks.

PHP MySQL Prepared Statements Class File

Introduction

This is a simply written lightweight class file for php developers to implement MySQL prepared statements in their project.

MySQl prepared statements are recommended to avoid SQL injection attacks.

There are so many advanced class files out there which will allow you to build your application without knowing anything about SQL queries but this one is for those who want to write their own queries but need a structured way to escape unwanted characters without writing redundunt code

What is SQL Injection?

SQL injection is a technic to exploit your queries by giving some malicious codes as values which may expose your database.

Let's assume you have a URL structure where you pass user id as a query parameter and show the details of that user and you don't use prepared statements.

For example, your url is http://example.com/details.php?id=1 and you use the following query

SELECT * FROM users WHERE id = 1;

The value is from the query parameter and user can easly edit the url and change it tohttp://example.com/details.php?id=0 OR 1=1. So now your query will be

SELECT * FROM users WHERE id = 0 OR 1=1;

So the above query will give away the details of all users in your database.

How to Use This Class File?

Click download button which will download mysqli-connect.php file and save it anywhere in your project folder and then include it in your page like below.


<?php
  require_once("path_to/mysqli-connect.php");
  $db = new DB("mysqli_user", "mysql_password", "mysql_db_name", "hostname");
?>

SELECT


<?php
  //Without WHERE Clause
  $r = $db->select("SELECT * FROM table", '', array());
  print_r($r)
  //With WHERE Clause
  $r = $db->select("SELECT * FROM test WHERE id = ? ", 'i', array(1));
  print_r($r);
?>
 

INSERT


<?php
  $r = $db->insert("INSERT INTO table(field1, field2) VALUES(?,?) ", 'ss', array('some', 'thing'));
  echo "Last Insert ID: ".$r;
?>

UPDATE


<?php
  $r = $db->update("UPDATE test SET a = ?, b = ? WHERE id = ?", 'ssi', array('some1', 'thing2', 5));
  echo "Affected Rows: ".$r;
?>

DELETE

If you take a look at the methods/functions in the class file update and delete functions are identical we just put it as separate functions for the sake of structuring you can change what type of data should be returned from any functions there.


<?php
  $r = $db->delete("DELETE FROM test WHERE id = ?", 'i', array(3));
  echo "Affected Rows: ".$r;
?>

RAW QUERY

In case, you want to execute a query without using prepared statements structure(not recommended)


<?php
  $r = $db->query("SELECT * FROM test");
  while($row = $r->fetch_assoc()){
    print_r($row);
  }
?>

Comments (0)

SUBSCRIBE TO OUR NEWSLETTER