Neverside

MySQL Record Search :: Tight Security :p

I was searching around the web for tips and pointers about searching for a string in a column in a mysql database.

I ran into this piece of code, and from what I can understand, this is pretty secure from sql injections and such. I am really looking forward to seeing what the pros have to say about it

And another thing, is there any other way of displaying the results? I mean I cant really like the way it displays it at the end. I'd rather RETURN the results then assigning it.

Some parts of this tutorial looks pretty outdated too :/

PHP:

<?php

function search_split_terms($terms){

$terms = preg_replace("/\"(.*?)\"/e", "search_transform_term('\$1')", $terms);
$terms = preg_split("/\s+|,/", $terms);

$out = array();

foreach($terms as $term){

$term = preg_replace("/\{WHITESPACE-([0-9]+)\}/e", "chr(\$1)", $term);
$term = preg_replace("/\{COMMA\}/", ",", $term);

$out[] = $term;
}

return $out;
}

function search_transform_term($term){
$term = preg_replace("/(\s)/e", "'{WHITESPACE-'.ord('\$1').'}'", $term);
$term = preg_replace("/,/", "{COMMA}", $term);
return $term;
}

function search_escape_rlike($string){
return preg_replace("/([.\[\]*^\$])/", '\\\$1', $string);
}

function search_db_escape_terms($terms){
$out = array();
foreach($terms as $term){
$out[] = '[[:<:]]'.AddSlashes(search_escape_rlike($term)).'[[:]]';
}
return $out;
}

function search_perform($terms){

$terms = search_split_terms($terms);
$terms_db = search_db_escape_terms($terms);
$terms_rx = search_rx_escape_terms($terms);

$parts = array();
foreach($terms_db as $term_db){
$parts[] = "content_body RLIKE '$term_db'";
}
$parts = implode(' AND ', $parts);

$sql = "SELECT * FROM Content WHERE $parts";

$rows = array();

$result = mysql_query($sql);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

$row[score] = 0;

foreach($terms_rx as $term_rx){
$row[score] += preg_match_all("/$term_rx/i", $row[content_body], $null);
}

$rows[] = $row;
}

uasort($rows, 'search_sort_results');

return $rows;
}

function search_rx_escape_terms($terms){
$out = array();
foreach($terms as $term){
$out[] = '\b'.preg_quote($term, '/').'\b';
}
return $out;
}

function search_sort_results($a, $b){

$ax = $a[score];
$bx = $b[score];

if ($ax == $bx){ return 0; }
return ($ax > $bx) ? -1 : 1;
}

function search_html_escape_terms($terms){
$out = array();

foreach($terms as $term){
if (preg_match("/\s|,/", $term)){
$temp[] = '"'.HtmlSpecialChars($term).'"';
}else{
$temp[] = HtmlSpecialChars($term);
}
}

return $out;
}

function search_pretty_terms($terms_html){

if (count($terms_html) == 1){
return array_pop($terms_html);
}

$last = array_pop($terms_html);

return implode(', ', $terms_html)." and $last";
}

#
# do the search here...
#

$results = search_perform($HTTP_GET_VARS[q]);
$term_list = search_pretty_terms(search_html_escape_terms(search_split_terms($HTTP_GET_VARS[q])));

#
# of course, we're using smarty 
#

$smarty->assign('term_list', $term_list);

if (count($results)){

$smarty->assign('results', $results);
$smarty->display('search_results.txt');
}else{

$smarty->display('search_noresults.txt');
}
?>

Source: http://iamcal.com/publish/articles/php/search/

___________________

xXxXxXx

Last edited by System_Failure, September 8th, 2004 02:30 AM (Edited 1 times)

O.o

www.php.net/mysql_escape_string works fine for me ::bandit::
and for spliting the terms:

PHP:

<?php
$terms = explode(" ", mysql_escape_string($search));
$search = '';

foreach($terms as $term){
     $search .= $term . '%';
}
// i just though that you can probably just do:
// $search = str_replace(' ', '%', $search);
$query = "SELECT * FROM `articles` WHERE `title` LIKE '%search'";
?>

___________________

-Developer
-Forum Leader
-NeverNET

Last edited by aonic, September 8th, 2004 03:00 AM (Edited 1 times)

Ahhh! Thanks! When I was doing the searches, I expected something like that, but I was kind of amazed when I found out the above functions

btw, on php.net, in the mysql_escape_string page, it says:

"Note: This function has been deprecated since PHP 4.3.0. Do not use this function. Use mysql_real_escape_string() instead."

___________________

xXxXxXx

cool

___________________

-Developer
-Forum Leader
-NeverNET

just make sure you don't escape more then once or you'll be pulling your hair in frustration wondering whats going on

___________________

There are 10 types of people in the world: those who understand binary, and those who don't.