Add MySQL Data In A Safe Way

This tutorial will present some PHP functions to be used when working with MySQL databases:

1. magic_quotes_gpc() – can be on from php.ini – adds \ character before simple or double quotes.
2. addslashes() – a PHP function that adds \ character before simple or double quotes.
3. myql_real_scape_string() – adds \ character before special characters like: NULL, \n, \r, ‘, “, \x00 and \x1a
4. htmlentities() – tranforms HTML chracters into entities of them. (for example: > becomes &gt).

These functions are very useful on preventing SQL injection when data is taken using “$_POST” or “$_GET”.
I will present three examples for function from 2., 3. and 4.

2.
<?php
// MySQL connection to database

// If data is sent for email and name from form.
if (isset($_POST['name']) && isset($_POST['email'])) {
$name = $_POST['name];
$email = $_POST['email'];

// We verify if "magic_quotes_gpc()" is ON
//If it is OFF we filter that data using addslashes()
if(!get_magic_quotes_gpc()) {
$name = addslashes($name);
$email = addslashes($email);
}

// now we can add the data in a much safer way using MySQL
$sql = "INSERT INTO `table_name` (col_name, col_email) VALUES ('$name', '$email')";

// If the query was not succesful we display the error.
if (!mysql_query($sql)) {
echo 'Error : '. mysql_errno(). ' - '. mysql_error();
}
else {
echo 'Data was added;
}
}
@mysql_close();
?>

3.

<?php
// database connection

// Same as above, if data is sent from form to be updated (in this case)
if (isset($_POST['name']) && isset($_POST['email'])) {
// if magic_quotes_gpc() is ON then we apply stripslashes() to not have two times "\"
if(get_magic_quotes_gpc()) {
$_POST['name'] = stripslashes($_POST['name']);
$_POST['email'] = stripslashes($_POST['email']);
}

//data filtered using mysql_real_escape_string()
$name = mysql_real_escape_string($_POST['name']);
$email = mysql_real_escape_string($_POST['email']);

$sql = "UPDATE `table_name` SET `col_name`='$name', `col_email`='$email' WHERE `id`=1";
if (!mysql_query($sql)) {
echo 'Error : '. mysql_errno(). ' - '. mysql_error();
}
else {
echo 'Data has been modified';
}
}
@mysql_close();
?>

Note! If you want to read the data from table that was added in the ways presented above you must use the function stripslashes();

<?php
// mysql connection

$sql = "SELECT `col_name`, `col_email` FROM `table_name`";
$result = mysql_query($sql) or trigger_error(E_USER_ERROR);
if (mysql_num_rows($result) == 0) {
echo 'No result found';
}
else {
while ($row = mysql_fetch_assoc($result)) {

$name = stripslashes($rand['col_name']);
$email = stripslashes($rand['col_email']);

echo '
'. $name. ' - '. $email;
}
}
@mysql_close();
?>

4. If you want to add HTML code in a MySQL database for safety you can use htmlentities().

<?php
// mysql connection

// Variabila care contine codul HTML pe care dorim sa-l adaugam in tabelul MySQL
$sir = 'PHP-MySQL';

$htmldata = htmlentities($htmldata);

$sql = "INSERT INTO `table_name` (col_html) VALUES ('$htmldata')";

if (!mysql_query($sql)) {
echo 'Error : '. mysql_errno(). ' - '. mysql_error();
}
else {
echo 'Data added';
}
@mysql_close();
?>

Note: In order to read correctly the data from MySQL that has been added after filtering with htmlentities() you must use html_entity_decode().

That’s it! I hope you enjoyed this PHP and MySQL tutorial that explained you how to increase the security of your code.
Thanks for reading!