What are all the ways to search your entire mysql table?

[mslilafowler]

Hi there

I was wondering how I could prevent this type of behaviour from users on my website, if they type something like mydomain?search=1 in the address bar, all of the rows from my table show up.

I have certain rules in place, such as, only display the rows which have a status > 1 etc, but that type of search overrides everything (it ignores the rules I set in the WHERE clause). How does that work?

The thing I’m concerned about is that users can search for ‘disabled’ or ‘blacklisted’ rows this way. Besides searching for * or 1, what are the other parameters users can use to search the entire table?

Thank you for stopping by.

[phdr]

It sounds like you are putting external/unknown data directly into the sql query statement, where sql special characters in the data can break the sql query syntax, which is how sql injection is accomplished.

To prevent this behavior, use prepared queries, with place-holder(s) in the sql query statement for the data, then supply the values when the query gets executed.

[mslilafowler]

I am using prepared statements…

As a quick fix I’m doing this:

//only allow searching if the search parameter fulfils these conditions
if (isset($_GET['search']) && !empty($_GET['search']) && $_GET['search'] != 1 && $_GET['search'] != '*') {
...

Looks kinda hacky but I have no idea how else to do it…

[phdr]

You will need to post code showing how your current query is being built and executed, because if it currently works correctly with a where clause with things like status > 1 and matching other column values, there’s no way just a submitted search value could override the hard-coded part of the query.

[mslilafowler]

It’s a bit of a complex piece of code…because of the pagination and filtering. But I’ll post it:

/*Pagination*/
$records_per_page = 20;
require 'pagination/vendor/autoload.php';
$pagination = new Zebra_Pagination();
$fetch_paginated_listings = '
    SELECT
        SQL_CALC_FOUND_ROWS
        *
    FROM
        listings 
    LEFT JOIN product_likes ON p_l_product_ref=l_ref 
    LEFT JOIN vendors ON v_ref = l_v_ref 
    WHERE l_v_ref IS NOT NULL 
    AND l_type <> 2 
    AND l_status = 2 
    AND v_status >= 1 
    AND l_end_date >= CURDATE() 
    '; //items which seller disabled (i.e. closed his shop) or deleted should not be shown
if (isset($_GET['search']) && !empty($_GET['search']) && $_GET['search'] != 1 && $_GET['search'] != '*') {
    //Users can search by:
    //Title
    //Seller Reference Code (which they can see publicly on any profile. not username, because usernames are likely to change, and people who bookmark urls will then have 404 errors when visiting it later.)
    $fetch_paginated_listings .= " AND l_title LIKE ? OR l_v_ref LIKE ? "; //check if there is any search queries in the url
    //MySQL uses short-circuit evaluation for the OR operator. In other words, MySQL stops evaluating the remaining parts of the statement when it can determine the result.
    $search_param = $_GET['search'];
    $passThis = "%" . $search_param . "%";
    $fetch_paginated_listings .= ' ORDER BY l_title ';    
    $fetch_paginated_listings .= ' 
    LIMIT
        ' . (($pagination->get_page() - 1) * $records_per_page) . ', ' . $records_per_page . '';
    $stmt_fetch_paginated_listings = $conn->prepare($fetch_paginated_listings);
    $stmt_fetch_paginated_listings->bind_param('ss', $passThis, $passThis);

} else {
    //if the user is not searching for anything / lands on this page without the search parameter:    
    if (isset($_COOKIE['pagination_seed'])) {
        $fetch_paginated_listings .= ' ORDER BY l_title '; // RAND('.$cookie_session_seed.') 
    } else {
        $fetch_paginated_listings .= ' ORDER BY l_modified_at ';
    }
    $fetch_paginated_listings .= ' 
    LIMIT
        ' . (($pagination->get_page() - 1) * $records_per_page) . ', ' . $records_per_page . '';
    $stmt_fetch_paginated_listings = $conn->prepare($fetch_paginated_listings);
}
$stmt_fetch_paginated_listings->execute();

[phdr]

The WHERE clause is a logic expression. It matches rows that evaluate to a true value. ORing anything with a TRUE value results in a TRUE.

You need to add ( ) around the OR’ed terms to force them to be evaluated first and then AND’ed with the rest of the WHERE clause -

$fetch_paginated_listings .= " AND (l_title LIKE ? OR l_v_ref LIKE ?) ";

The existing query ANDs the l_title LIKE ? with the WHERE terms on the left, but then ORs all that with the l_v_ref LIKE ? value.

[mslilafowler]

Wow that’s genius, so in fact the rule was correctly overriding the other rules… That’s so silly of me.

Man, Thank you so much The query is working exactly as expected now!