2

I just got hand over with hundreds of SQL views, stored procedures and functions I like to see data visually to better understand what the object does.

Can I assume if I run a select statement and execute the query - there is no chance that it modifies record/data?

I am just worry that there are some SQL tricks that can updates data via select statement.

Anything to watch out for inside the select? Any tips or keyword that I have to look out for?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
BobNoobGuy
  • 1,551
  • 2
  • 30
  • 62
  • 1
    if anything doesnt make sense just EXECUTE sp_helptext 'ObjectName' will give you the definition of that object see inside whats going on. Nothing to worry about Views but I would suggest to see Stored Procs and Function Definitions before you touch them. – M.Ali Dec 10 '13 at 18:48

2 Answers2

2

A Plain old SELECT statement by itself won't update any data.

Even if the SELECT statements has calls into 1 or more FUNCTIONs, you can rest assured that none of those FUNCTIONs will be modifying any data in your DATABASE**

** See http://technet.microsoft.com/en-us/library/ms191320.aspx The 1st limitation of Functions listed is

User-defined functions cannot be used to perform actions that modify the database state.

I even tried writing a FUNCTION that did an INSERT before returning a return value, and the error I got was as follows. [Thanks to @Aaron and @John for keeping me honest].

Msg 443, Level 16, State 15, Procedure CRUDFunction, Line 5
Invalid use of a side-effecting operator 'INSERT' within a function.
Shiva
  • 20,575
  • 14
  • 82
  • 112
  • 3
    A function can't perform side effects, so unless they are doing something crazy like [looping back via `xp_cmdshell`](https://sqlblog.org/blogs/denis_gobo/archive/2008/05/08/6703.aspx)... – Aaron Bertrand Dec 10 '13 at 18:38
  • thanks for reminding functions. Looks like there are only 20 - 30 functions and they all labelled accordingly. – BobNoobGuy Dec 10 '13 at 18:39
1

A normal, basic SELECT does not modify data. However, there are a few things you should keep an eye out for.

If the code uses dynamic SQL to build a SELECT statement, you must worry about a SQL injection attack. Unless you are careful about parameterizing dynamic SQL, an attacker can do pretty much anything that the current login can do. For example, DROP TABLE, DROP DATABASE, etc. See the linked article on Wikipedia.

Furthermore, beware that a SELECT with the INTO clause can be used to create a table and insert records into it. See this question and Microsoft TechNet for more information.

Community
  • 1
  • 1
Paul Williams
  • 16,585
  • 5
  • 47
  • 82
  • how does that answer his question. He said nothing in the question about SQL Injection Attack. – logixologist Dec 10 '13 at 20:27
  • The OP asked if there were SQL tricks that can cause a `SELECT` statement to update data. I would say a SQL Injection attack definitely qualifies as a "trick" that someone can use to force a dynamic SQL `SELECT` statement to change data. Also, note that I edited my answer to mention SELECT INTO. – Paul Williams Dec 10 '13 at 20:41
  • +1 for remembering the SELECT INTO! Thanks for updating your answer. – logixologist Dec 10 '13 at 21:13