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 Code: [ Download ] [ Select ]

 
 
 
<?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);
 
?>
 
 
 
 

1.  
2.  
3.  
4. <?php
5.  
6.  
7.  
8. // Start the SQL string
9.  
10. $sql = 'SELECT * FROM users_info WHERE ';
11.  
12.  
13.  
14. // Add each parameter if it's needed
15.  
16. // General search
17.  
18. if (isset($_GET['textfieldSearch'])) {
19.  
20. $sql .= '(user_name LIKE \'%'. $_GET['textfieldSearch'] .'%\' OR ';
21.  
22. $sql .= 'email_address LIKE \'%'. $_GET['textfieldSearch'] .'%\' OR ';
23.  
24. $sql .= 'telephone LIKE \'%'. $_GET['textfieldSearch'] .'%\' OR ';
25.  
26. $sql .= 'user_surname LIKE \'%'. $_GET['textfieldSearch'] .'%\' OR ';
27.  
28. $sql .= 'speciality LIKE \'%'. $_GET['textfieldSearch'] .'%\') AND ';
29.  
30. }
31.  
32.  
33.  
34. if (isset($_GET ['select_speciality'])) {
35.  
36. $sql .= 'speciality LIKE \'%'. $_GET ['select_speciality'] .'%\' AND ';
37.  
38. }
39.  
40.  
41.  
42.  
43.  
44. if (isset($_GET['select_company'])) {
45.  
46. $sql .= 'firm_company LIKE \'%'. $_GET['select_company'] .'%\' AND ';
47.  
48. }
49.  
50.  
51.  
52. if (isset($_GET['select_surname'])) {
53.  
54. $sql .= 'user_surname LIKE \'%'. $_GET['select_surname'] .'%\' AND ';
55.  
56. }
57.  
58.  
59.  
60. if (isset($_GET['select_country'])) {
61.  
62. $sql .= 'user_country LIKE \'%'. $_GET['select_country'] .'%\' AND ';
63.  
64. }
65.  
66.  
67.  
68.  
69.  
70. // Finish the SQL - 1. Remove any ending AND or WHERE
71.  
72. if (substr($sql, strlen($sql) - strlen('WHERE ')) == 'WHERE ') {
73.  
74. $sql = substr($sql, 0, strlen($sql) - strlen('WHERE '));
75.  
76. }
77.  
78. if (substr($sql, strlen($sql) - strlen('AND ')) == 'AND ') {
79.  
80. $sql = substr($sql, 0, strlen($sql) - strlen('AND '));
81.  
82. }
83.  
84.  
85.  
86.  
87.  
88. // Finish the SQL - 2. Add the order by
89.  
90. $sql .= ' ORDER BY user_name ASC';
91.  
92.  
93.  
94. print_r($sql);
95.  
96.  
97.  
98.  
99.  
100. // Perform the search
101.  
102. mysql_select_db( $my_connection);
103.  
104. $RS_search_country = mysql_query($sql, $my_connection) or die(mysql_error());
105.  
106. $row_RS_search_country = mysql_fetch_assoc($RS_search_country);
107.  
108. $totalRows_RS_search_country = mysql_num_rows($RS_search_country);
109.  
110. ?>
111.  
112.  
113.  
114.  

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.