Page 1 of 1

HELP: Parse a SQL query, do some change, compse a new query

Posted: Fri Nov 21, 2008 3:18 am
by cysin
Hello everyone, is there any way to parse a SQL query, and do some change, adding some where conditions for example, and then compose a new SQL query?
The first thing that came into my mind is using ORM, because they can add any condition or do whatever you want with a SQL query. But I don't know how to convert a SQL query into ORM object (Doctrine, or Propel).

I don't know if my idea is feasible, and anyone can help?

Any help is appreciated and thanks in advance~

Re: HELP: Parse a SQL query, do some change, compse a new query

Posted: Fri Nov 21, 2008 4:32 am
by novice4eva
I was stuck with similar problem too few months back, i searched like hell but end up with nothing. Luckily my sql was simple enough(SELECT...FROM...WHERE...) without inner selects so i just exploded the sql and re-made the sql, now i think about it, i took the hard way, i could have just put the whole sql as inner sql like SELECT....FROM ($Sql) table WHERE ....

Re: HELP: Parse a SQL query, do some change, compse a new query

Posted: Fri Nov 21, 2008 4:49 am
by cysin
I was planning to modify the SQL directly, but I found there were too much SQL to deal with.

I want to write a simple SQL parse to do it but it is really hard to make sure it works properly with all SQL queries, especially complex one~

Re: HELP: Parse a SQL query, do some change, compse a new query

Posted: Fri Nov 21, 2008 5:27 am
by novice4eva
Won't it work, you know put a wrapper around your complex SQL?? I mean like i said earlier, something like :
select table.column1,table.column2,..... FROM (MY COMPLEX SQL) table WHERE ADDITIONAL CONDITION.
I think you can work around adding the additional conditions even tables like this. But i don't know if it will suit your requirement.

Re: HELP: Parse a SQL query, do some change, compse a new query

Posted: Fri Nov 21, 2008 7:15 pm
by cysin
For example, there are lots of SQL queries, like the following:
SELECT * FROM members WHERE memberID=34
UPDATE articles SET author='john', time=NOW() WHERE articlID=67
...
...

Now, I need to do something with the queries, add a where condition "siteID=2" to each of the queries, so the above queries will be:

SELECT * FROM members WHERE siteID=2 AND memberID=34
UPDATE articles SET author='john', time=NOW() WHERE siteID=2 AND articlID=67

So here comes the problem, it is simply a manipulation of strings, but how do I add the "siteID=2" to each of the SQL queries? Using regular expression, or something else like a SQL parser?

Maybe I can write a scripts to deal with simple SQL like SELECT * FROM table WHERE, but how to do with the complex queries, like join, union etc, I am not sure how to achieve that.

I really need help~~

Re: HELP: Parse a SQL query, do some change, compse a new query

Posted: Sat Nov 22, 2008 10:58 pm
by novice4eva
Unless there are 2 or more selects in a single query it must not be a problem, so i will exclude the idea that joins will cause problem, it shouldn't. And exploding with 'WHERE' although might seem like easy but the word 'where' can come at any part of a query, i avoided that by using 1=1, i mean i strictly followed this:
SELECT .... FROM ... WHERE 1=1 AND ....
so i exploded with "WHERE 1=1"....
I was thinking of exploding with select first, but again we will never know if it will work perfectly since also word 'select' can be part of anything(table name, columns ..) in a query!!