-1

I am looking for all the possible positions in a sql statement that a sql function can be called. It is my first time to use sql functions and in my code I need to detect whether users' input contains sql function, if so, the input is not valid.

The position in sql statement means SELECT clause or other clause like FROM, WHERE......

I am using SQL Server

So is there any suggestions for detect sql functions in a sql statement or all the possible positions for the function in a statement?

Cherry Wu
  • 3,844
  • 9
  • 43
  • 63
  • What are you using to validate the user input (and where)? is this just in a stored procedure (or something) and you're building a dynamic query or...? and when you say "sql function" do you mean absolutely no functions? like they can't use `datepart` or anything like that? can you give examples of what valid and invalid input look like? – gloomy.penguin Sep 18 '13 at 18:40
  • I'm not sure how you could check that exactly.... "`I am typing(text)`" would be valid if `typing` is not a function. To get a list of all user defined functions you can do something like `SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'` and use that to compare but I'm not sure how you could check for system functions (like `datepart` or something) without hardcoding them. Or -- (_risky unless you're sure the input is safe_), you could try to run the input and see if the value changes... if it does, a function might have been used. – gloomy.penguin Sep 18 '13 at 19:00
  • @gloomy.penguin, I write C# to understand sql statement(this is the input), if there is any sql function in it, the input sql statement is not valid. In order to detect where are the sql functions in a sql statement, I have to know its possible positions in SQL statement first (like, in SELECT clause, or FROM clause, or any other positions) – Cherry Wu Sep 18 '13 at 19:01

2 Answers2

1

If I understood your question, you are asking how and where to check about SQL code entered by the user.

Where:
I think you might need to check it in the user interface, check the textbox or files or whatever the input stream of the user is. This shall not be done in the sql server ASAIK

How:
usually you can use parameters in your SQL statements so that any value passed by the user is passed by a parameter and if it contains a SQL code, it will not be executed.

Kasparov92
  • 1,365
  • 4
  • 14
  • 39
1

SQL functions are allowed in any place in a SELECT where a column is allowed, i.e. anywhere in the SELECT list, in WHERE, JOIN, GROUP BY, HAVING, ORDER BY.

You'll need a SQL parser to know for sure if it's a function...

meda
  • 45,103
  • 14
  • 92
  • 122
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Hi @dnoeth, do you know how to use sql parser to detect functions in different ? Currently I can detect functions in Select clause by using the GetType() method of SelectColumn class, but still looking for methods work for other clauses – Cherry Wu Sep 27 '13 at 05:09
  • I can't help you regarding a SQL parser :-( It's probably quite complex, you might search the net for one in your preferred programming language. – dnoeth Sep 27 '13 at 05:22