php

How to Escape Single Quote in PHP/MySQL

As a PHP/MySQL developer, you have certain practices worth noting and making use of every other time. One of the is how to escape single quote in PHP while working with MySQL database. In this article, I will illustrate how to escape Single Quote in PHP/MySQL

Escaping refers to the process of encoding data containing characters so that MySQL interprets it correctly. To do this, you MUST escape strings with a PHP function known as mysql_real_escape_string. This means that you have to run this function in PHP before passing your query to the database. Normal good practice is to escape any data that comes into your database from an eternal source so as to avoid potential SQL injection.

You have to escape your data before you build your query. Also, you can build your query programmatically using PHP’s looping constructs and range:

Example 1: Using Object Oriented style

While using Object Oriented method, you escape characters in strings as shown below:

<?php
$conn = new mysqli("localhost","db_username","bb_password","your_db");

if ($conn -> connect_errno) {
  echo "Failed to connect to MySQL: " . $conn -> connect_error;
  exit();
}

// Escape special characters, if any
$fname = $conn -> real_escape_string($_POST['studentname']);
$lname = $conn -> real_escape_string($_POST['lastname']);
$grade = $conn -> real_escape_string($_POST['grade']);

$quest="INSERT INTO students (fName, LName, grade) VALUES ('$fname', '$lname', '$grade')";
 $stmt = $conn->prepare('SELECT * FROM items WHERE category = ?');
 $stmt->bind_param('s', $categ); 

 $stmt->execute();

if (!$conn -> query($quest)) {
  printf("%d Row inserted Successfully!\n", $conn->affected_rows);
}

$conn -> close();
?>



Advertisement

Example 2: Using Procedural Method

<?php
$conn = mysqli_connect("localhost","db_username","bb_password","your_db");

if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  exit();
}

// Escape special characters, if any
$fname = mysqli_real_escape_string($conn, $_POST['studentname']);
$lname = mysqli_real_escape_string($conn, $_POST['lastname']);
$grade = mysqli_real_escape_string($conn, $_POST['grade']);

$quest="INSERT INTO students (fName, LName, grade) VALUES ('$fname', '$lname', '$grade')";

if (!mysqli_query($conn, $quest)) {
  printf("%d Row inserted.\n", mysqli_affected_rows($conn));
}

mysqli_close($conn);
?>
Advertisement

How it Works

Definition and Usage

The real_escape_string() / mysqli_real_escape_string() function escapes special characters in a string for use in an SQL query, taking into account the current character set of the connection. For example, while working with strings that use single quotes, like people’s names e.g. O’Neil, you need to handle this by the use of the real_escape_string() / mysqli_real_escape_string() function.

This function is used to create a legal SQL string that you can use in an SQL statement. The given string is encoded to produce an escaped SQL string, taking into account the current character set of the connection.

Related Posts

Facebook

Get the Facebook Likebox Slider Pro for WordPress