I was working on a search function for the module I was assigned to. The contents stored in the database isn’t plaintext, but rather encoded in base64. There’s currently no native MySQL functions to handle base64 data so that means I can’t make inline text and regex comparisons.
I thought hard about it and came up with 2 solutions:
- Save the data in plaintext instead and use heavy data validation
- Retrieve all rows and store them in a PHP array and do my regex magic there
Option 1 was out of the question, because in every part of the module, we’ve made sure to protect against XSS (cross site scripting). Going with this option will always risk creating a security hole regardless of how carefully I validate the supplied data.
Option 2 would be more secure and would allow me to apply my regex skills to the fullest extent, but it would cost more memory overhead because I’d have to retrieve the rows, store them into memory while doing the pattern matching…
Until I thought of option 3 (well, more like option 2.1). What I’d do is to limit the fetched rows by imposing more conditions, say for example, retrieve the rows that the currently logged user is only allowed to see. Then perform my regex thingamabob on those filtered results.
I had another problem though, doing this means I have to work on some sort of system to weigh the relevance of the results. Since this relevance value is computed after the database retrieval, this means I’ll be adding it into an extra column in the result array. The result array I am supposed to work on is a multidimensional array. It would have a similar structure as this example:
$data = array(
array(
'name'=>'Chuck',
'color'=>'Blue'
),
array(
'name'=>'Rupert',
'color'=>'Black'
),
array(
'name'=>'Cerrillo',
'color'=>'Red'
),
array(
'name'=>'Abarro',
'color'=>'White'
)
); The above array is a 2-dimensional array. This means that echo $data[0]['name']; will display “Chuck”.
Anyway, as I earlier mentioned, I am supposed to add a relevance value to each row (or what I’d like to call weight). In addition, I might add some custom fields to some rows. Here’s how that array would look like with the additional fields and arbitrary weight values. (take note of the custom field added in line #6)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| $data = array(
array(
'weight'=>20,
'name'=>'Chuck',
'color'=>'Blue',
'custom'=>'Boo!'
),
array(
'weight'=>10,
'name'=>'Rupert',
'color'=>'Black'
),
array(
'weight'=>40,
'name'=>'Cerrillo',
'color'=>'Red'
),
array(
'weight'=>30,
'name'=>'Abarro',
'color'=>'White'
)
); |
By my weight system, the “heavier” a row is, the more relevant it is to the search query. So I should sort it by weight in a descending order.
PHP has a native array function called array_multisort();, so I tried that one. It doesn’t work with my array because the array sizes are not the same. I gave up a while after looking for workarounds to get it to work with that function, so I decided to write my own sorting function for arrays like these.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
| /*
* 2-Dimensional Array Sort by Chuck Cerrillo (August 26, 2009)
* Sorts a 2-dimensional array according to a given field/index.
* This is particularly useful for sorting post-processed MySQL data (i.e. where
* custom fields have been added AFTER the retrieval of table data, and you'd need
* to resort the results).
*
* For more information on how to use this array, please visit my blog entry at
* http:///blog.chuckcerrillo.com/2009/08/sorting-uneven-2-dimensional-arrays/
*/
function array_2dsort($array, $field, $sort = 'asc'){
$temp = array();
// First we grab the index names of the sub-array
// by walking through the array
if(is_array($array)){
foreach($array AS $subarrayindex=>$subarray){
if(is_array($subarray))
foreach($subarray AS $index=>$key){
// Store the fieldnames into the $fieldnames array
$fieldnames[$index][] = $subarray[$index];
// We then store the field names into a temporary array
${$index}[$subarrayindex] = $key;
}
}
// Sort the temporary array based according to the $sort
// argument, and maintain index associations by using
// asort() or arsort()
if(strtolower($sort) == 'asc')
asort($$field);
else
arsort($$field);
// Now we recreate a new array with the desired order
foreach($fieldnames AS $fieldname=>$fieldindex){
foreach($fieldindex AS $rowindex=>$rowvalue){
foreach(${$field} AS $index=>$value){
$result[$index][$fieldname] = $array[$index][$fieldname];
}
}
}
}
return $result;
} |
Now this one works perfectly according to my needs. This function takes in 3 parameters,
$array – the array to sort (required)$field – the field or index to sort by (required)$sort – the sorting order, either asc or desc, defaults to asc (optional)
Given the above array, I would call my function this way – $data = array_2dsort($data,'weight');. If you would do a print_r($data);, it would give you this: (note that I didn’t specify ‘desc’ as my sorting order)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
| Array
(
[1] => Array
(
[weight] => 10
[name] => Rupert
[color] => Black
[custom] =>
)
[0] => Array
(
[weight] => 20
[name] => Chuck
[color] => Blue
[custom] => Boo!
)
[3] => Array
(
[weight] => 40
[name] => Abarro
[color] => White
[custom] =>
)
[2] => Array
(
[weight] => 50
[name] => Cerrillo
[color] => Red
[custom] =>
)
) |
This function automagically adds blank fields to rows that do not contain them.
