What is this search string convention?
April 23, 2013 4:00 PM Subscribe
I tried to search a database at the place I volunteer with REGEX, but it didn't work. The database interface has some built-in options to search, so I have been messing with those trying to figure out what conventions it will allow. Can you tell me what this is?
When I search a field for entries matching begins with STRING, it shows that it searched for: STRING%
Ends with STRING: %STRING
Then anywhere shows up as %STRING%
I've only messed with a few databases before but they've all allowed for REGEX and this one doesn't. This also seems less precise than what I'm used to. So what is this? And does this match with any conventions that will allow me to search for something like [a-z] would be in REGEX? Or use things like + or *? I tried a handful of different ideas but nothing worked.
I've asked the people above me and they don't know anything about the database. I am waiting to see if I get a response from the person who made the database but I'm really curious and want to know if this is even worth pursuing if it's not easy to get in contact with the person who handles it. Thanks.
When I search a field for entries matching begins with STRING, it shows that it searched for: STRING%
Ends with STRING: %STRING
Then anywhere shows up as %STRING%
I've only messed with a few databases before but they've all allowed for REGEX and this one doesn't. This also seems less precise than what I'm used to. So what is this? And does this match with any conventions that will allow me to search for something like [a-z] would be in REGEX? Or use things like + or *? I tried a handful of different ideas but nothing worked.
I've asked the people above me and they don't know anything about the database. I am waiting to see if I get a response from the person who made the database but I'm really curious and want to know if this is even worth pursuing if it's not easy to get in contact with the person who handles it. Thanks.
Response by poster: That is definitely a good candidate! I'm surprised I've never run into a SQL database the places I've volunteered yet, so I didn't recognize it. Thanks!
It looks like it won't let me do [charlist] searches, though, and SQL supports that I thought? I will see if I can find out if that's just a limitation of the interface...
posted by Nattie at 4:11 PM on April 23, 2013
It looks like it won't let me do [charlist] searches, though, and SQL supports that I thought? I will see if I can find out if that's just a limitation of the interface...
posted by Nattie at 4:11 PM on April 23, 2013
do you know what database the system uses? Oracle, Postgres, MSSQL, and MySQL all have different string matching capabilities. the baseline SQL matching is just "%" for a zero-length-or-longer and "?" for a one-character wildcard.
also, even if the underlying database supports richer pattern matching, the application may not.
posted by russm at 4:30 PM on April 23, 2013
also, even if the underlying database supports richer pattern matching, the application may not.
posted by russm at 4:30 PM on April 23, 2013
I have used several systems which were SQL backend but made to hide that fact from the user. They tended to accept % as a wildcard but have a lot of other inputs sanitised to stop you messing with the data.
It is very possible that it is SQL based but you're not allowed to get into the guts of it too much.
posted by Just this guy, y'know at 4:33 PM on April 23, 2013
It is very possible that it is SQL based but you're not allowed to get into the guts of it too much.
posted by Just this guy, y'know at 4:33 PM on April 23, 2013
Best answer: This sounds like it's using the SQL LIKE operator, not a regular expression.
Typically LIKE supports two special characters:
LIKE does not support things like character classes ([abc]), or grouping, or any of the other nice things you might do with a regular expression.
You could see if you can use ' or " to do SQL injection and generate more complex queries that way, but hopefully that's not possible since it represents a serious security vulnerability.
posted by danielparks at 4:36 PM on April 23, 2013 [1 favorite]
Typically LIKE supports two special characters:
- % — Match 0 or more characters
- _ — Match any 1 character
LIKE does not support things like character classes ([abc]), or grouping, or any of the other nice things you might do with a regular expression.
You could see if you can use ' or " to do SQL injection and generate more complex queries that way, but hopefully that's not possible since it represents a serious security vulnerability.
posted by danielparks at 4:36 PM on April 23, 2013 [1 favorite]
Try ANY('STRING%','%STRING','%STRING%')
if that doesn't work try IN('STRING%','%STRING','%STRING%')
Unlikely, but worth a shot.
posted by soelo at 9:02 PM on April 23, 2013
if that doesn't work try IN('STRING%','%STRING','%STRING%')
Unlikely, but worth a shot.
posted by soelo at 9:02 PM on April 23, 2013
Here is the documentation for LIKE in PostgreSQL. Everything in this section is SQL:2011 standard (the default escape seems to have changed from SQL-92). If you have the opportunity, switching to POSIX regexes will give you more expressive power, in a language worth learning.
posted by Tobu at 9:47 PM on April 23, 2013
posted by Tobu at 9:47 PM on April 23, 2013
This thread is closed to new comments.
posted by soelo at 4:02 PM on April 23, 2013