1

So I'm trying to wrap my head around how to combine three conflicting things (bound parameters, regex, partial-match-searching using user input) securely, and I am not sure I have discovered the right/secure way to deal with these things. This shouldn't be an uncommon concern, but the documentation that deals with the intersection of all three security factors for PDO & php is either hard to find or non-existent.

My needs are relatively simple and standard, in order of priority:

  1. I want to prevent sql injection (currently I'm using bound parameters)
  2. I want to prevent regex injection
  3. I want to search with partial matching using user input strings

So for example, I want to allow a user to search through usernames with a case insensitive partial match, e.g. A search for Xiu will bring up the username Xiu and Xiulu and also xiuislowercase

Currently I have the following statement:

select * from users where username ilike :search_string || '%'

and elsewhere, I use more complex cases using the regex operator similarly to:

select * from users where username ~* :search_string || '%'

Where :search_string is a bound statement in php pdo, and the database is postgresql. This performs the right search, returns the right results, and I'm reasonably certain that it is proof against sql injection since it's a bound parameter. However, I'm not certain that it is proof against regex injection, and I have no idea how to make it proof against regex injection at the same time as having it be proof against sql-injection.
How would I completely secure it against regex injection as well, using php, PDO, and postgresql?

Kzqai
  • 22,588
  • 25
  • 105
  • 137

1 Answers1

2

LIKE does not support regular expressions, it only has limited pattern-matching metacharacters % and _. So if you escape those two characters with a backslash in the string before you pass it the parameter value, you should be safe.

<?php
$search_string = preg_replace('/[%_]/', '\\\\$0', $search_string);
$pdoStmt->execute(array('search_string'=>$search_string));

Alternatively, you could compare a left-substring of the username to your input, then it's comparing against a fixed string with no metacharacter pattern-matching features.

select * from users where left(username, :search_string_length) = :search_string

Re your comment:

The general rule to avoid code injection is: never execute arbitrary user input as code.

This applies to SQL injection of course, which is why we use parameters to force user input to be interpreted as values, and not modify the syntax of the SQL statement.

But it also applies to the "code" in a regular expression string within an SQL operation. A regular expression is itself a type of code logic, it's a very compact representation of a finite state machine for matching input.

The solution to avoid code injection is that it's okay to let user input choose code (as in whitelisting), but don't let the user input be interpreted as code.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Ah, I apologize, I simplified the example more than I should have, I guess. I would like to be able to work with `~*` as well as like, I was thinking that they had the same problems but yes, `like` is an easier case to deal with. This whole problem arose because I found that when I bound parameters for ~* they were succeptible to regex-injection and when I ed to surround them with " quotes, I was no longer able to effectively bind them as parameters. Perhaps the solution when using `~*` is simply to escape all regex characters like you suggest with `like`, but that seems error prone... – Kzqai Dec 28 '12 at 20:50
  • Fair enough, I was hoping to get some method of dealing with partial regex/partial user search situations, but I suppose I'm probably better off just skipping such situations altogether and only using like when I'm utilizing user input. Though I can't think of a case when I'll actually want to use a sql regex without any kind of user info being involved. – Kzqai Dec 31 '12 at 15:40