[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Is this secure enough?

I just wrote a quick search form but I want to see if this is secure enough. Can anyone please take a look a quick look. Thanks
mysql_connect ("", "","")  or die (mysql_error());
mysql_select_db ("");

$search = mysql_real_escape_string($_POST['search']);

$sql = mysql_query("SELECT * FROM apartments WHERE contact LIKE '%$search%' OR phone LIKE '%$search%' OR office LIKE '%$search%' OR town LIKE '%$search%' OR cross_streets LIKE '%$search%' OR description LIKE '%$search%' OR email LIKE '%$search%' OR rent LIKE '%$search%' order by `date_created`");

echo "<strong>Click Headers to Sort</strong>";
	echo "<table border='0' align='center' bgcolor='#999969' cellpadding='3' bordercolor='#000000' table class='sortable' table id='results'> 
<th> Title </th> 
<th> Rent </th>
<th> Bed </th>
<th> Bath </th>
<th> Contact </th> 
<th> Office </th> 
<th> Phone </th> 

while ($row = mysql_fetch_array($sql)){
echo "<tr>
		<td bgcolor='#FFFFFF' style='color: #000' align='center'>
		   <a href='classified/searchapts/index.php?id=".$row['id']."'>" . $row['title'] . "</a></td>
		<td bgcolor='#FFFFFF' style='color: #000' align='center'>" . $row['rent'] . "</td>
		<td bgcolor='#FFFFFF' style='color: #000' align='center'>" . $row['rooms'] . "</td>
		<td bgcolor='#FFFFFF' style='color: #000' align='center'>" . $row['bath'] . "</td>
		<td bgcolor='#FFFFFF' style='color: #000' align='center'>" . $row['contact'] . "</td> 
		<td bgcolor='#FFFFFF' style='color: #000' align='center'>" . $row['office'] . "</td> 
		<td bgcolor='#FFFFFF' style='color: #000' align='center'>" . $row['phone'] . "</td> 

echo "</table>"; 


Open in new window

  • 3
  • 2
1 Solution
Yes and no...
mysql_real_escape_string does escape special character strings such as \x00, \n, \r, \, ', " and \x1a. Although there are other ways a user can hack into your system using SQL injection.

One of the best ways to protect yourself against SQL injection attacks is to use prepared statements with parameters, but before you even get to the database, you can filter the input itself (in this case the search string).

One simple way of doing that would be as follows:
$search = preg_replace('/[^\w\'\" ]/i', '', $_POST['search']);

Open in new window

The above regular expression will REMOVE any characters that are not Alphanumeric, space or quotes (as commonly used in searches). Any other character you can think of (such as <, >, (, ), ;, = etc) as may be used in XSS attacks are stripped out.

You can easily add more safe characters to this list if you require, like hyphen, plus comma, period etc... (/[^\w\'\"\-\+\,\. ]/i), however the more accurate you are with your filtering, the safer you will be.

This together with the mysql_real_escape_string function give you a reasonably safe query string to work with.

Hope this helps...
genesisvhAuthor Commented:
Thank you for the response but I would like the user to search emails, and phone numbers. When I implemented this I wasn't able to search for both. I guess I will need the @,- and space so one can do the search. How can I modify the above code to search for emails and phone numbers? Thanks
sure, the regex you'd be looking for would look like this:
$search = preg_replace('/[^\w\'\"\@\-\.\, ]/i', '', $_POST['search']);

Open in new window

Note, in the above code, the \w is shorthand in Regular Expressions for "A-Z, a-z, 0-9 and _"
So the whole regex would allow the following characters...
A-Z, a-z, 0-9, underscore (_), comma(,), period (.), hyphen (-), @ and spaces...

if you want to allow brackets for area-codes in your searches as well, then simply add "\(\)" to the list of characters inside the square brackets...
/[^\w\'\"\@\-\.\,\(\) ]/i

Open in new window

Remember, you also need to keep your mysql_real_escape_string() in the code, so you might do this...
$search = mysql_real_escape_string(preg_replace('/[^\w\'\"\@\-\.\, ]/i', '', $_POST['search']));

Open in new window

genesisvhAuthor Commented:
genesisvhAuthor Commented:
Thanks for your help double_helix!

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now