Sanitizing user input in PHP with MySQLi Escape String Function

When you have an online application containing a form to get inputs from a user, it is important to sanitize the inputs to avoid errors and to protect your database against SQL injection attacks. In PHP, there is a function called mysqli_real_escape_string() that escapes special characters in a string. Those special characters include a single quote, double quote, backslash, NUL, and line return, which have meaning to the SQL.

For example, we have an SQL statement to search for customers with the last name O’Malley:

1. SELECT * FROM customers WHERE last_name = 'O'Malley';

The above statement causes an error since there are three single quotes, and MySQL does not know where our string ends. The single quote in the last name needs to be “escaped” so that it will be treated as only data and not a special character in MySQL. To escape the string, we need to add a backslash before all single quotes in the string.

1. SELECT * FROM customers WHERE last_name = 'O\'Malley';

We can add the backslash manually if we already know what the string is. However, we cannot do that with dynamic data. For example, if the string is from a user’s input, we need a code that handles the escaping dynamically. In PHP, there is a function that does that for us, named mysqli_real_escape_string().

mysqli_real_escape_string() function

Syntax

1. mysqli_real_escape_string(connection, escapestring);

Parameter

The function returns the escaped string.Return value

PHP version

5+

We can only use this function when we have a connection to the database. Please note in the syntax that the first parameter is the database connection.

Why is the database connection needed? The mysqli_real_escape_string() function uses the connection to get the information about the character set used in the database. It is necessary to know how the string should be treated.

Example

Please see the below image. Let’s say we have a simple page using PHP to search customer full name based on the input parameter last name.

Example of mysqli_real_escape_string() in PHP

Here’s the HTML form for the page above:

1. <form method="get" action="<?php echo $_SERVER['PHP_SELF']; ?>">
2. <p>
3. <label for="searchtext">Enter a last name:</label>
4. <input type="search" name="searchtext" id="searchtext">
5. <input type="submit" name="search" value="Search">
6. </p>
7. </form>

The string in the textbox will be passed to the server and used in an SQL statement to get the customer data from the database. Please see the PHP code below:

1. <?php
2. if (isset($_GET['search'])) {
3. $db = new mysqli('localhost','root','password','dbname');
4. if ($db->connect_error) {
5. $error = $db->connect_error;
6. }
8. $searchtext = $_GET['searchtext'];
9. $sql = "SELECT * FROM customers WHERE last_name = '$searchtext'";
11. $result = mysqli_query($db, $sql);
12. }
13. ?>

There is an $sql variable that contains an SQL statement to query the customers’ data. The search text from the user input has been embedded directly in the SQL select query, and this can be potentially harmful.

For example, if the search text contains a single quote such as O’Malley, it will return an error since MySQL will treat the single quote as a special character.

So, let’s pass the $_GET[‘searchterm’] as an argument to the mysqli_real_escape_string() function so that the $searchtext variable becomes like this below:

1. $searchtext = mysqli_real_escape_string($db, $_GET['searchtext']);
2. $sql = "SELECT * FROM customers WHERE last_name = '$searchtext'";

Now the $searchtext has value O\’Malley instead of O’Malley, and the SQL code will work.

Another example, imagine someone is doing an SQL injection and inputting a string like this:

1. '; DROP TABLE payments; --

As we already applied the escape string function, the $sql string becomes:

1. SELECT * FROM customers WHERE last_name = '\'; DROP TABLE payments; -- '

As we can see, the string DROP statement is treated as just data, and the query won’t do any harm by removing the table payments from our database. The database is being protected from SQL injection attacks.

The difference with mysql_real_escape_string()

There is a function called mysql_real_escape_string() (without “i”) which has similar name with mysqli_real_escape_string(). This function also escapes special characters from a string. However, this function is deprecated in PHP 5 and was removed from PHP 7.

Syntax

1. mysql_real_escape_string(escapestring);

Example

1. <?php
2. $searchtext = "O'Malley";
3. $lastname = mysql_real_escape_string($lastname);
5. $query = "SELECT * FROM customers WHERE last_name = '$lastname'";
6. ?>

Please note that the syntax does not require parameter database connection which is different from mysqli_real_escape_string().

An alias with shorter name: mysqli_escape_string()  

The only problem with mysqli_real_escape_string() is that the name is super long. There is an alias to this function called mysqli_escape_string(). Imagine if we need to write many SQL statements using the functions. Using an alias with a shorter name saves our time.

How get all values in a column using PHP?

I 've been searching for this everywhere, but still can't find a solution:
How do I get all the values from a mySQL column and store them in an array?

For eg: Table Name: Customers Column names: ID, Name # of rows: 5

I want to get an array of all the 5 names in this table. How do I go about doing that? I am using PHP,
and I was trying to just:

SELECT names FROM Customers

and then use the

mysql_fetch_array

PHP function to store those values in an array.

 Answers

This would work, see more documentation here :
 http://php.net/manual/en/function.mysql-fetch-array.php

$result = mysql_query("SELECT names FROM Customers");
$storeArray = Array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $storeArray[] =  $row['names'];  
}
// now $storeArray will have all the names.

---

I would use a mysqli connection to connect to the database. Here is an example:

$connection = new mysql("127.0.0.1", "username", "password", "database_name", 3306);

The next step is to select the information. In your case I would do:

$query = $connection->query("SELECT `names` FROM `Customers`;");

And finally we make an array from all these names by typing:

$array = Array();
while($result = $query->fetch_assoc()){
    $array[] = $result['names'];
}

print_r($array);

So what I've done in this code: I selected all names from the table using a mysql query.
Next I use a while loop to check if the $query has a next value.
If so the while loop continues and adds that value to the array '$array'.
Else the loop stops. And finally I print the array using the 'print_r' method
so you can see it all works. I hope this was helpful.

---

Step 1

First get the mysql extension source which was removed in March:

https://github.com/php/php-src/tree/PRE_PHP7_EREG_MYSQL_REMOVALS/ext/mysql

Step 2

Then edit your php.ini

Somewhere either in the “Extensions” section or “MySQL” section, simply add this line:

extension = /usr/local/lib/php/extensions/no-debug-non-zts-20141001/mysql.so

Step 3

Restart PHP and mysql_* functions should now be working again.

Step 4

Turn off all deprecated warnings including them from mysql_*:

error_reporting(E_ALL ^ E_DEPRECATED);

Now Below Code Help You :

$result = mysql_query("SELECT names FROM Customers");
$Data= Array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
{
        $Data[] =  $row['names'];  
}

You can also get all values in column using mysql_fetch_assoc

$result = mysql_query("SELECT names FROM Customers");
    $Data= Array();
    while ($row = mysql_fetch_assoc($result)) 
    {
            $Data[] =  $row['names'];  
    }

This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. 

Instead, the MySQLi or PDO_MySQL extension should be used.

Reference

---

YOU CAN USE MYSQLI ALTERNATIVE OF MYSQL EASY WAY

---

*

<?php
$con=mysqli_connect("localhost","my_user","my_password","my_db");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$sql="SELECT Lastname,Age FROM Persons ORDER BY Lastname";
$result=mysqli_query($con,$sql);

// Numeric array
$row=mysqli_fetch_array($result,MYSQLI_NUM);
printf ("%s (%s)\n",$row[0],$row[1]);

// Associative array
$row=mysqli_fetch_array($result,MYSQLI_ASSOC);
printf ("%s (%s)\n",$row["Lastname"],$row["Age"]);

// Free result set
mysqli_free_result($result);

mysqli_close($con);
?> 

MySQLi functions to fetch records from resultset.

In previous articles we already learned how to connect MySQL database and how to perform different queries using MySQLi. This article demonstrates some MySQLi functions which are used to retrieve row by row data from resultset. The complete article is divided into two parts , this part provides the explanation and implementation details of mysqli_fetch_row() and mysqli_fetch_array() functions in both Procedural and Object oriented style. 

1. mysqli_fetch_row()

mysqli_fetch_row() fetches a row from the resultset and returns row data as an enumerated array.

Syntax :

a. Procedural Style : mysqli_fetch_row( result )

    result : Specifies mysqli result set.(Required)

b. Object Oriented Style : mysqli_result::fetch_row()

Description : mysqli_fetch_row() fetches a row from the resultset and returns the row data as an array with numeric indexes starting from 0. Next subsequent call fetch the next row from result set. The function return NULL if there are no more rows in the resultset.

Result : This function returns an array of strings which corresponds to row data fetched from resultset and it return NULL if there are no more rows in the resultset.

Example : Procedural style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 2";

if ($result = mysqli_query($con, $query))
{
    echo "<pre>";
    while($res = mysqli_fetch_row($result)){
    print_r($res);
    }    
    echo "</pre>";
    mysqli_free_result($result);
}
?>

Example : Object Oriented style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 2";
if ($result = $con->query($query)) 
{
   echo "<pre>";
   //----fetch rows from resultset----
   while ($res = $result->fetch_row()) {
        print_r($res);
    }
   echo "</pre>";
   $result->free();
}
?>

Output :

Array
(
    [0] => 1
    [1] => Amit
    [2] => Kumar
    [3] => amit.kumar21@gmail.com
    [4] => Dehradun
)
Array
(
    [0] => 2
    [1] => Ravi
    [2] => Singh
    [3] => ravi22singh@yahoo.com
    [4] => Mumbai
)

2. mysqli_fetch_array()

mysqli_fetch_array() function also used to fetch row from the resultset. It fetches a row from the resultset and return row data as an array of numeric, associative or combination of both types.

Syntax :

a. Procedural Style : mysqli_fetch_array( result, resulttype );

result : Specifies mysqli result set.(Required)

resulttype :  Constant value which defines the type of the returned array.(Optional)

resulttype can have one of the following values :

MYSQLI_NUM : Return a numeric array.

MYSQLI_ASSOC : Return an associative array.

MYSQLI_BOTH : Return an array with both numeric and associative keys.

b. Object Oriented Style : mysqli_result::fetch_array ( resulttype )

resulttype :  Constant value which defines the type of the returned array.

Description : mysqli_fetch_array() fetches a row from the specified resultset result and returns the row data as an array of type specified by passing second optional parameter resulttype. The function returns an array with both numeric and associative keys as the default, if resulttype parameter is not passed. The column names of resultset used as keys for associative array.

The name of fields returned by function are case sensitive. If in a resultset two or more columns have the same name, then the value of the last column overwrites the value of an earlier column with the same name. To get values of multiple columns with the same name it is better to use MYSQLI_NUM type.

Result : This function returns an array of row data fetched from the resultset. It returns NULL, if there are no more rows in resultset.

Example : Procedural Style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 3";

if ($result = mysqli_query($con, $query))
{
    echo "<pre>";
    echo "Using MYSQLI_NUM : <br>";
    $res = mysqli_fetch_array($result,MYSQLI_NUM);
    print_r($res);
    echo "Using MYSQLI_ASSOC : <br>";    
    $res1 = mysqli_fetch_array($result,MYSQLI_ASSOC);
    print_r($res1);
    echo "Using MYSQLI_BOTH : <br>";    
    $res = mysqli_fetch_array($result,MYSQLI_BOTH);
    print_r($res);        
    echo "</pre>";
    mysqli_free_result($result);
}
?>

Example : Object Oriented Style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 3";
if ($result = $con->query($query)) 
{
   echo "<pre>";
   echo "Using MYSQLI_NUM : <br>";
   $res = $result->fetch_array(MYSQLI_NUM);
   print_r($res);
   echo "Using MYSQLI_ASSOC : <br>";    
   $res1 = $result->fetch_array(MYSQLI_ASSOC);
   print_r($res1);
   echo "Using MYSQLI_BOTH : <br>";    
   $res = $result->fetch_array(MYSQLI_BOTH);
   print_r($res);        
   echo "</pre>";
   $result->free();
}
?>

Output :

Using MYSQLI_NUM :
Array
(
    [0] => 1
    [1] => Amit
    [2] => Kumar
    [3] => amit.kumar21@gmail.com
    [4] => Dehradun
)
Using MYSQLI_ASSOC :
Array
(
    [id] => 2
    [first_name] => Ravi
    [last_name] => Singh
    [email] => ravi22singh@yahoo.com
    [city] => Mumbai
)
Using MYSQLI_BOTH :
Array
(
    [0] => 3
    [id] => 3
    [1] => Deepak
    [first_name] => Deepak
    [2] => Rawat
    [last_name] => Rawat
    [3] => deepak.rawat@gmail.com
    [email] => deepak.rawat@gmail.com
    [4] => New Delhi
    [city] => New Delhi
)

This article demonstrates two more functions mysqli_fetch_assoc() and mysqli_fetch_object() which are also used to fetch rows from the result set. The details of each function are given below :

1. mysqli_fetch_assoc()

mysqli_fetch_assoc() function retrieves a row from a result set and returns the row data as an associative array.

Syntax :

a. Procedural Style : mysqli_fetch_assoc(result)

result : Specifies mysqli result set.(Required)

b. Object Oriented Style : mysqli_result::fetch_assoc()

Description : mysqli_fetch_assoc() is used to retrieve the data of result row as an associative array where each key represents a column name of the result set. The function returns NULL if there are no more rows in resultset.

Result : This function returns the fetched row as an associative array where each key of array represents a column name in the result set. If there are no more rows to retrieve in the result set, then function returns NULL.

Note : If two or more columns have the same name, the value of the last column overwrites the values of earlier columns.

Example : Procedural Style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 2";

if ($result = mysqli_query($con, $query))
{
    echo "<pre>";
    while($res = mysqli_fetch_assoc($result)){
  print_r($res);
    } 
    echo "</pre>";
    mysqli_free_result($result);
}
?>

Example : Object Oriented Style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 2";
if ($result = $con->query($query)) 
{
   echo "<pre>";
   while($res = $result->fetch_assoc()){
      print_r($res);    
   }
   echo "</pre>";
   $result->free();
}
?>

Output :

Output :
Array
(
    [id] => 1
    [first_name] => Amit
    [last_name] => Kumar
    [email] => amit.kumar21@gmail.com
    [city] => Dehradun
)
Array
(
    [id] => 2
    [first_name] => Ravi
    [last_name] => Singh
    [email] => ravi22singh@yahoo.com
    [city] => Mumbai
)

2. mysqli_fetch_object()

mysqli_fetch_object() function fetches the current row of a result set as an object.

Syntax :

a. Procedural Style : mysqli_fetch_object(result, classname, params)

result : Specifies mysqli result set.(Required)

classname : Specifies the name of the class to instantiate. Default an object of stdClass is returned.(Optional)

params : Specifies an array of parameters to pass to the constructor of classname object.(Optional)

b. Object Oriented Style :  mysqli_result::fetch_object (class_name, params)

classname : Specifies the name of the class to instantiate.(Optional)

params : Specifies an array of parameters to pass to the constructor of classname object.(Optional)

Description : mysqli_fetch_object() function retrieves the current row from a result set and return it as an object. The columns of the result row are represent by object properties.

Result : This function returns an object which represents the current row of the result set. It returns NULL if there are no more rows in the result set.

Example : Procedural Style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 2";

if ($result = mysqli_query($con, $query))
{
    echo "<pre>";
    while($object = mysqli_fetch_object($result)){
  echo $object->id.". ".$object->first_name." ".$object->last_name." from ".$object->city;
  echo "<br>";
    } 
    echo "</pre>";
    mysqli_free_result($result);
}
?>

Example : Object Oriented Style

<?php
//--------SQL statement---------
$query = "SELECT * FROM users LIMIT 2";
if ($result = $con->query($query)) 
{
   echo "<pre>";
   while($object = $result->fetch_object()){
      echo $object->id.". ".$object->first_name." ".$object->last_name." from ".$object->city;
      echo "<br>";    
   }
   echo "</pre>";
   $result->free();
}
?>

Output :

1. Amit Kumar from Dehradun
2. Ravi Singh from Mumbai

There is one other function mysqli_fetch_all() which fetch all rows from the result set, but it needs the mysqlnd driver installed before using it as mentioned in the given link : http://www.php.net/manual/en/mysqli-result.fetch-all.php#88031

mysqli_fetch_all()

mysqli_fetch_all() fetch all rows from the result set and returns rows as an array of associative, numeric or both types.

Syntax :

a. Procedural style : mysqli_fetch_all(result, resulttype)

result : Specifies mysqli result set.(Required)

resulttype :  An optional constant value which defines the type of the returned array.(Optional)

One of the following constant values can be used for the resulttype : 

MYSQLI_NUM : Returns a numeric array.

MYSQLI_ASSOC : Returns an associative array.

MYSQLI_BOTH : Returns an array with both numeric and associative keys.

b. Object Oriented Style : mysqli_result::fetch_all (resulttype)

resulttype : An optional constant value which defines the type of the returned array.(Optional)

Description : mysqli_fetch_all() looks similar to mysqli_fetch_array() function as it also returns fetched rows as an array of  numeric, associative and combination of both types. The main difference between these two functions is mysqli_fetch_all() fetches all rows at once while mysqli_fetch_array() fetches one row at a time.

Result : This function fetches all rows from the result set and returns a multidimensional array containing numeric or associative arrays of rows data fetched from the result set.