CODEWITHSUNDEEP
javascriptjquery
Vivek
Vivek

Reputation: 2101

Jquery Regular Expressions

I would like to search for a particular pattern in my SQL , so that it starts only with SELECT and not with INSERT , UPDATE , DELETE.

How can i achieve this using JQuery , I would like to use JQuery to do a case insensitive matching of the string.

EDIT

I would also like to consider subqueries also.

Therefore my requirement is that the query should not contain any INSERT , UPDATE , DELETE statements anywhere. It should contain only SELECT statments

Upvotes: 0

Views: 780

Answers (2)

Niklas B.
Niklas B.

Reputation: 95308

I'll use a whitelist for that, rather than a blacklist:

function isValidQuery(str) {
  return /^select\s+([a-z_*]+,?)+\s+from\s+[a-z_]+$/i.test(str);
}

This only matches queries of the form SELECT field, [field, ...] FROM table. This can also be extended to allow certain kinds of WHERE or GROUP BY specifiers. If this isn't enough, you could also use a much stricter blacklist:

function isValidQuery(str) {
  if (/delete|update|insert|truncate|create|drop/i.test(str)) return false;
  // possibly more excluding rules...
  return true;
}

If you want to protect your data with this, I certainly wouldn't rely on the second method, as it can probably be circumvented (you forgot a dangerous command, SQL smuggling, ...) A much better approach would be to create a dedicated database user that only has read access to the tables you want to be available via this interface.

Upvotes: 1

jabclab
jabclab

Reputation: 15042

You could use something like the following:

function isSelect(str) {
    return /^select/i.test(str) && !/insert|update|delete.*/.test(str);
}

isSelect("select * from foo"); // true
isSelect("SELECT id, name from foo");  // true
isSelect("update ..."); // false
isSelect("DELETE ..."); // false

Upvotes: 1

Related Questions