PHP/MySQL- Escape All POST Or GET Variables For MySQL INSERT

Feb 16th, 2010
Maruf scribbled this post.

I’m quickly going to demonstrate how to escape special characters in a string for all POST/GET variables safe for a MySQL INSERT query when passing values from a HTML form.

So, let’s say we have a form like this on index.php:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

<form action="insert.php" method="POST">
<fieldset>
<legend>Details<legend>
<div>
<label>First Name:</label>
</div>
<div>
<input type="input" name="first_name" value="Bright" disabled="disabled" />
</div>
<div>
<label>Surname:</label>
</div>
<div>
<input type="input" name="surname" value="Cherry" disabled="disabled" />
</div>
<div>
<label>Email:</label>
</div>
<div>
<input type="input" name="email" value="design@brightcherry.co.uk" disabled="disabled" />
</div>
<div style="margin-top:10px;">
<input disabled="disabled" type="submit" value="Submit" />
</div>
</fieldset>
</form>

The form’s action is insert.php, so whichever method you choose (GET/POST) will get passed to insert.php. Now, before you INSERT the data into a MySQL table, you should ensure the data is safe to INSERT.

The PHP function you should use is mysql_real_escape_string.

You can do the following before inserting the data (assuming the method type is POST):

1
2
3

$_POST['first_name'] = mysql_real_escape_string($_POST['first_name']);
$_POST['surname'] = mysql_real_escape_string($_POST['surname']);
$_POST['email'] = mysql_real_escape_string($_POST['email']);

But that’s not a very efficient method because you’re repeating the same function over and over, and if the form you’re using has a lot more fields, it will quickly become very problematic for a number of reasons.

Here’s a better and more efficient way of cleaning the data:

1
2
3
4

foreach(array_keys($_POST) as $key)
{
  $clean[$key] = mysql_real_escape_string($_POST[$key]);
}

So what’s happening now is that ALL the POST variables are being cleaned in the foreach loop. You’re also renaming the POST values to $clean, but keeping the actual key value the same – so to echo the values you simply do this:

1
2
3

echo $clean['first_name'];
echo $clean['surname'];
echo $clean['email'];

It’s as easy as that