CODEWITHSUNDEEP
c#sql
Jonny
Jonny

Reputation: 2519

Is there a simple way to check whether a SQL statement will update the database?

I have a chunk of code that does something like this:

if (sqlStatement.WillUpdateDatabase)
  DoThing1();
else 
  DoThing2();

Currently WillUpdateDatabaseis implemented as 

public bool WillUpdateDatabase {
  get { return statementText.StartsWith("SELECT"); }
}

This catches the majority of cases, but it gets more complicated with SELECT ... INTO .... And there are possibly a few other cases that I might need to take into account.

Just to be clear: this is not to implement any type of security. There are other systems that check for SQL injection attacks, this bit of code just needs to make a choice whether to do thing1 or thing2.

This seems like it should be a solved problem. Is there an industry standard way to do this reliably?

Update/clarification: Something like UPDATE Table1 SET Column1 = 'a' WHERE 1 = 2 should be treated as an update.

Upvotes: 1

Views: 397

Answers (2)

Louis 'LYRO' Dupont
Louis 'LYRO' Dupont

Reputation: 1112

Since your statement text will change values only with the SQL queries beginning with UPDATE or INSERT, one way to test if you query will try to update could be the following :

public bool WillUpdateDatabase {
    get { return (statementText.StartsWith("UPDATE") || statementText.StartsWith("INSERT") ) }
}

But you cannot know if the query will effectively update some fields in the table. As an example, if your query is like UPDATE persons SET age = 25 WHERE name = "John" and there is no entry with name John, your query will try to update, but will obviously not be able to because there is nothing to update.

EDIT

Thanks to @NikBo and @Gusman, I replaced Contains with StartsWith to avoid any issue like Nik Bo explained in the comments bellow.

Upvotes: -2

Caius Jard
Caius Jard

Reputation: 74595

As many others have commented, this really is a nasty problem and inspecting the SQL isn't really ever going to cut it for you because you'll practically end up writing an entire SQL parser (and that really would be reinventing the wheel). You'll probably have to make a database user that only has read permissions for all tables, then actually execute the query you want to test using that read-only user and catch the situations where it fails because of permission violations, (rather than SQL syntax etc)

Upvotes: 3

Related Questions