PHP MySQL Negative Wildcard Character List

Question

To secure a HTML GET method form submission so that users cannot use MySQL wildcards to get the whole table values, I was looking for a list of PHP-MySQL wildcard characters.

For example, this GET method URL takes lowrange & highrange values as 1 and 100 respectively, and generates the appropriate results between that range: example.com/form.php?lowrange=1&highrange=100

But my table values may range from -10000 to +10000, & a smart alec may like to get the whole list by changing the URL as example.com/form.php?lowrange=%&highrange=% (or other special characters like *, ?, etc. etc.)

The basic purpose is to not allow anything that can lead to whole db values getting exposed in one shot.

So far, I've found the following characters to be avoided as in the preg_match:

if(preg_match('/^~`!@#$%\^&\*\(\)\?\[\]\_]+$/',$url)) {
  echo "OK";
}
else {
  echo "NOT OK";
}

Any other characters to be included in the list to completely block the possibility of wildcard based querying?

There are string fields & numbers fields. String field have LIKE matching (where field1 like '%GET-FORM-VALUE%'), & nos. fields have equal to and BETWEEN matching (where field2 = $GET-FORM-VALUE, OR where field3 between $GET-FORM-LOVALUE and $GET-FORM-HIVALUE) $in SQL.

Thank you.

@Machavity - Thanks, I am having multiple fields, & each need to be validated to avoid such special chars. There are string fields and nos. fields, which need to be validated - like strings should not have % or ?, or nos should be allowed between particular ranges only. In summary, let's just take it as numbers (with equal to & between mataching) & strings fields with LIKE matching. — levent001
– levent001, Commented Jan 20, 2023 at 13:19
Wildcards only work if you use LIKE comparison in the first place. Makes no sense to use that operator for your min/max ranges to begin with - >/>= and </<= are the operators you want to use for that. And for text fields - well either you look for absolute strict equality, simply with =; or you'd need to give us a proper explanation how exactly you want to search in the first place. — C3roe
– C3roe, Commented Jan 20, 2023 at 13:21
In light of the edits... why wouldn't a prepared statement suffice here? You can use your values safely that way, regardless of what control characters are put in. — Machavity
– Machavity ♦, Commented Jan 20, 2023 at 13:32
Besides all of the rest of this, you might also want to look into pagination and/or just using LIMIT — Chris Haas
– Chris Haas, Commented Jan 20, 2023 at 13:37

Aquaholic · Accepted Answer · 2023-01-21 06:20:53Z

No doubt that Prepared Statements are the best implementation, & MUST be the norm.

But sometimes, one gets into a "tricky scenario" where it may not be possible to implement it. For example, while working on a client project as external vendor, I was required to do similar implementation, but without having access to the code that made the connection (like, execute_query was not possible to implement, as connection to db was differently set in another config file). So I was forced to implement the "sanitization" of incoming form values.

To that, the only way was to check what data type & values were expected, & what wild characters can be used to exploit the submission. If that is the case with you, then the alternate solution for your situation (String LIKE matching) & (numbers EQUAL TO or BETWEEN 2 given numbers) is as follows:

As soon as form is submitted, at backend first thing to do is:

Put a check for alphabets on String, BLOCK percentage sign & underscore.

if (preg_match('/[^A-Za-z]+/', $str) && !(preg_match('/%/',$strfield))) { // all good...proceed to execute the query } else { // error message }
Similarly, put a check for numbers/floats on number fields, like if (preg_match('/[^0-9]+/', $nofield))

Only if above are satisfied, then proceed to connect to database, and run the query. Add more checks on field to prevent other wild-cards, as needed.

Another option I implemented (may not necessarily fit, but mentioning as food for thought): In addition to above checks, first generate a count of records that fit the query. If count is abnormally high, then either throw error asking user to narrow the range by resubmitting, or display a limited records per page making it cumbersome for them to keep clicking.

Again to reiterate, go for Prepared Statements if you can.

Collectives™ on Stack Overflow

PHP MySQL Negative Wildcard Character List

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related