2

How can I validate a string and know if it is a valid SQL where clause. Ex of invalid string: WHERE 1=1 AND EMPRESA = 'CMIP' AND CD_MAQ ==="jjj" Ex of valid string :WHERE 1=1 AND EMPRESA = 'CMIP' AND CD_MAQ ="jjj"

Appreciate your help.

Leonel Matias Domingos
  • 1,922
  • 5
  • 29
  • 53
  • There are so many complex things which could appear in a `WHERE` clause (e.g. a subquery). In general, you would need to write a parser to handle your problem; there is no quick and dirty solution AFAIK. – Tim Biegeleisen Jul 05 '17 at 08:18
  • 1
    Why is this required? Are you passing in where clauses externally? If so, you are not doing it right. Only parameters should be passed in. – Gurwinder Singh Jul 05 '17 at 08:19
  • Note: If you are passing a WHERE clause in your search page - YOU ARE DOING IT WRONG. Please use parameterized queries.... and read up on SQL Injection: https://stackoverflow.com/questions/601300/what-is-sql-injection !!!!!!!!!!!!! – Milney Jul 05 '17 at 08:36

2 Answers2

0

Sorry you cannot do this easily... Unless you fancy writing a SQL Parser in Javascript.

One solution would be to post the query to a backend which runs it in a try catch block and returns if there was an error or not.

I dread to think what the regex/rules list for this validation would be doing it manually.

Why do you want to do this?? Explain your actual goal rather than your suggested solution and we can maybe come up with a better idea... If you are passing a WHERE clause to SQL from a search page as you suggest - this is not the correct way to do it...

Please use Parameterized queries instead.

Also read up on SQL Injection: What is SQL injection?

Milney
  • 6,253
  • 2
  • 19
  • 33
  • Sadly, most likely just passing a `WHERE` clause to a query parser would _always_ error out. – Tim Biegeleisen Jul 05 '17 at 08:19
  • Yes sorry I presumed it was in the context of a query. It doesn't actually make sense at all to validate without a query as column references etc won't be able to be validated – Milney Jul 05 '17 at 08:28
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - [From Review](/review/low-quality-posts/16616362) – Carl Binalla Jul 05 '17 at 09:04
  • @Swellar - how does it not? I mentioned a solution... (posting to backend) – Milney Jul 05 '17 at 09:32
0

This is a complicated problem, you're essentially wanting to do is write an SQL interpreter. Obviously these already exist (on database systems of course) but also there are libraries that can do this for you. For Javascript see:

https://github.com/forward/sql-parser

Although, you probably don't want to perform security critical validation only on the client side, although it won't hurt to do it there and then also validate in on the server side before running it (if that is the end goal).

Thomas Cook
  • 4,371
  • 2
  • 25
  • 42
  • This is kind of a JS ORM that also manages relationships .Where clause is constructed on frontend using complex structures and lodash librarie. – Leonel Matias Domingos Jul 05 '17 at 08:27
  • Note that this will only work for very simple selects - "Currently it is only capable of parsing fairly basic SELECT queries but full SQL support will hopefully come in time.".... so probably not work for most where clauses – Milney Jul 05 '17 at 08:29
  • But where clause is always simple,could be long, but simple. No subquery, no IN clause, etc... – Leonel Matias Domingos Jul 05 '17 at 08:29
  • Surely an ORM should have enough knowledge of the structure of the database to create its own clauses based on the predicates provided? – Phylogenesis Jul 05 '17 at 08:32
  • just want to display " Invalid search " if where clause not valid. I have this advanced search that accepts SQL Operators – Leonel Matias Domingos Jul 05 '17 at 08:34
  • This is not an ORM... no idea where you got that from? It's a basic SQL parser that supports simple select statements. And WHERE clause can have subqueries, not sure why my answer was downvoted.... – Thomas Cook Jul 05 '17 at 08:34
  • @ThomasCook My point was actually directed to Leonel. If it's an ORM it should be generating the clauses itself from predicate information provided, not asking the user to write the clause itself. – Phylogenesis Jul 05 '17 at 08:39
  • @Phylogenesis yea and my comment was directed to Leonel as well, he was the one who said the library is an ORM, I was just saying it's not (as you were as well) – Thomas Cook Jul 05 '17 at 10:31