Re: [Squirrel-sql-users] Invalid SQL is executed

[Robert M. <rob...@gm...>]

On 3/21/07, Mark Noten <mar...@se...> wrote:
> Hi all,
>
> When executing the below invalid SQL statement, all the 89,867 records in the database were updated in stead of just a couple rows.
>
> update gid set changed=0, link_change=0, response_status='ACC' where cni_id in (select cni_id from cni where cuscar_id=4274);
>
> 89,867 Row(s) Updated
> Query 4 of 4 elapsed time (seconds) - Total: 1,675.695, SQL query: 1,675.695, Building output: 0
>
> The cni_id column does not exist in the cni table. In stead of raising an exception, the invalid SQL update statement was executed without the where condition. Needless to say that updating 89,867 records with production data did not receive a warm welcome. ;-)
>
> Can anyone explain me why this invalid query is executed in stead or raising an error? I'm using Squirrel SQL Client Version 2.3 final with a Oracle OCI Driver and oracle.jadbc.driver.OracleDriver from the ojdbc14.jar.
>

I don't know of any issues in 2.3 that would produce this problem.
What do you get when you just execute:

select cni_id from cni where cuscar_id=4274;

Did you only highlight "update gid set changed=0, link_change=0,
response_status='ACC' " when you ran the query?  Or, could it be that
there were two end-of-lines between the first part and the where
clause, like:

update gid set changed=0, link_change=0, response_status='ACC'

where cni_id in (select cni_id from cni where cuscar_id=4274);

If so and you had the cursor on just the the top line, I can see how
that would happen.  The query tokenizer interprets "\n\n" as a
statement separator in addition to ";".

Rob