2
\$\begingroup\$

Is the code below safe from SQL injection?

<?

mysql_connect ("localhost", "db","pass")  or die (mysql_error());
mysql_select_db ("db");

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

$sql = mysql_query("select * from tblListings where category like '%$term%' or title like '%$term%' or postcode like '%$term%' or info like '%$term%' ");

function highlight($needle, $haystack)
{
    return preg_replace('/(' . preg_quote($needle, '/') . ')/i', '<mark>$1</mark>', $haystack);
}

while ($row = mysql_fetch_array($sql)){

    echo '<br/> Category: ' . highlight($term, $row['category']);
    echo '<br/> Title: ' . highlight($term, $row['title']);
    echo '<br/> Address: ' . highlight($term, $row['add1']);
    echo '<br/> Street: ' . highlight($term, $row['street']);
    echo '<br/> City: ' . highlight($term, $row['city']);
    echo '<br/> Postcode: ' . highlight($term, $row['postcode']);
    echo '<br/> Phone: ' . highlight($term, $row['phone']);
    echo '<br/> E-Mail: ' . highlight($term, $row['email']);
    echo '<br/> Website: ' . highlight($term, $row['website']);
    echo '<br/> Info: ' . highlight($term, $row['info']);
    echo '<br/><br/>';
    //echo '<br/> E-Mail:   '.$row['email']; use this for the fields that you dont want to search

}

?>
\$\endgroup\$
  • 1
    \$\begingroup\$ As far as I know it is safe from SQL injection, but it is vulnerable to HTML/Javascript injection (xss attacks) unless the details in the database are explicitly stored in HTML form. If they are stored as plain text then you need htmlspecialchars() or similar when outputting them as html. \$\endgroup\$ – bdsl Feb 22 '15 at 16:14
  • \$\begingroup\$ You also should escape % and _ because they're wildcard characters in LIKE expression. You can test this by searching for %. \$\endgroup\$ – Alexey Lebedev Jun 4 '15 at 13:00
2
\$\begingroup\$

I HIGHLY suggest using PHP PDO with prepared statements ALONG with filtering each field for specific accepted characters.

But to answer your question, your method above is no longer considered safe and can be exploited.

PDO & Tutorial:

You can use preg_match to filter for specific character sequences.

Basic Regex Filter Example:

if (preg_match('/^[0-9-]+$/', $_POST['postcode'])) 
{
    //POSTAL CODE IS GOOD TO GO
    //Filters for charaters "0-9" along with "-" for longer postal codes
}   
else 
{
    //REJECT
}
\$\endgroup\$
  • 1
    \$\begingroup\$ If he's using Unicode UTF8, then this code is safe. It's better practise to use PDO however. But there's no need to panic people by saying the code can be exploited, when it cannot. \$\endgroup\$ – soupagain Feb 2 '12 at 13:06
  • \$\begingroup\$ @soupagain Interesting.. I've never heard of this UTF8 deal. What makes that secure and how do you force everything to be UTF8? I don't think many people know about that as I've read tons of PHP SQL tutorials/articles/questions without any mention of using specific encoding to make things secure. I could be remembering wrong though. Thanks! \$\endgroup\$ – PiZzL3 Feb 2 '12 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.