Is this secure enough?

Posted on 2011-05-11
Last Modified: 2012-05-11
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

Question by:genesisvh
    LVL 3

    Expert Comment

    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...

    Author Comment

    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
    LVL 3

    Accepted Solution

    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


    Author Closing Comment


    Author Comment

    Thanks for your help double_helix!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
    The viewer will learn how to count occurrences of each item in an array.
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now