PHP / MYSQL search feature

June 27th, 2007, 2:00 am

Hi There

I have a form which enables to search for users in a database ( kind of directory )


* text field where user can enter any keywords

* a drop down to select the user's country

* a drop down to select the user's speciality

* a drop down to select the user's surname



I want the user to be able to use one and any other criteria, or just one criteria.

At the moment the AND feature works but I run into problems when using only one criteria.
If I search for a surname only , and if the user I am searching for doesn't have a speciality recorded in the database, the result is blank.


For information, here is my code ( I am aware of the SQL injection problem but. but this is for a small closed network )

Any advised would be welcome, Thanks

Chris

[php]

<?php

// Start the SQL string
$sql = 'SELECT * FROM users_info WHERE ';

// Add each parameter if it's needed
// General search
if (isset($_GET['textfieldSearch'])) {
$sql .= '(user_name LIKE \'%'. $_GET['textfieldSearch'] .'%\' OR ';
$sql .= 'email_address LIKE \'%'. $_GET['textfieldSearch'] .'%\' OR ';
$sql .= 'telephone LIKE \'%'. $_GET['textfieldSearch'] .'%\' OR ';
$sql .= 'user_surname LIKE \'%'. $_GET['textfieldSearch'] .'%\' OR ';
$sql .= 'speciality LIKE \'%'. $_GET['textfieldSearch'] .'%\') AND ';
}

if (isset($_GET ['select_speciality'])) {
$sql .= 'speciality LIKE \'%'. $_GET ['select_speciality'] .'%\' AND ';
}


if (isset($_GET['select_company'])) {
$sql .= 'firm_company LIKE \'%'. $_GET['select_company'] .'%\' AND ';
}

if (isset($_GET['select_surname'])) {
$sql .= 'user_surname LIKE \'%'. $_GET['select_surname'] .'%\' AND ';
}

if (isset($_GET['select_country'])) {
$sql .= 'user_country LIKE \'%'. $_GET['select_country'] .'%\' AND ';
}


// Finish the SQL - 1. Remove any ending AND or WHERE
if (substr($sql, strlen($sql) - strlen('WHERE ')) == 'WHERE ') {
$sql = substr($sql, 0, strlen($sql) - strlen('WHERE '));
}
if (substr($sql, strlen($sql) - strlen('AND ')) == 'AND ') {
$sql = substr($sql, 0, strlen($sql) - strlen('AND '));
}


// Finish the SQL - 2. Add the order by
$sql .= ' ORDER BY user_name ASC';

print_r($sql);


// Perform the search
mysql_select_db( $my_connection);
$RS_search_country = mysql_query($sql, $my_connection) or die(mysql_error());
$row_RS_search_country = mysql_fetch_assoc($RS_search_country);
$totalRows_RS_search_country = mysql_num_rows($RS_search_country);
?>

[/php]

July 2nd, 2007, 10:13 am

Small and enclosed or not, SQL injection isn't that difficult to fight. It's just mysql_real_escape_string(). In your case, that is all you need.

And what query strings does this fail on? Give an example.