Re: Invalid SQL is executed

On 3/21/07, Mark Noten <mark.noten@xxxxxxxxxx> 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

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys-and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV