Hi gurus, i have a question about SQLServer

SQL Server Developer >> Hi gurus, i have a question about SQLServer

Hi everybody, i have a problem with SQLServer 2000

I have an application that allow that the user send a SQL
statement, I'm currently validating the input string should be only
SELECT , i do that with LTRIM and then a substring of 6 character must
be equal to "SELECT". (the user that connect my database don't have
permission to write data).

If the user send a bad SQL , mean a table does not exists or a
invalid field, i need to captured the message within a store procedure
(I can't use ODBC to do it, or any language ). the problem is when a
bad sql arrive to "sp_executesql" the SQLServer abort the execution
an cant capture the desire message.

There is way to control that in SQL server 2000 or other approach to
do this. i am dealing with proprietary application and can't edit the
source.

Thank in advance

SQL Server179

SQL Server Developer >> Hi gurus, i have a question about SQLServer
> I have an application that allow that the user send a SQL
> statement, I'm currently validating the input string should be only
> SELECT , i do that with LTRIM and then a substring of 6 character must
> be equal to "SELECT". (the user that connect my database don't have
> permission to write data).

You should not do this. The proper way to manage permissions is to
grant the user only the needed permissions, not by validating the
statements they try to execute. You will never be able to write a good
SQL parser (that allows everything that may be acceptable and denies
everything that is not acceptable). And even if you think that you got
it right, the risk is to big to allow the user to execute anything in
the database, without making sure that the right permissions are in
place.

> If the user send a bad SQL , mean a table does not exists or a
> invalid field, i need to captured the message within a store procedure
> (I can't use ODBC to do it, or any language ). the problem is when a
> bad sql arrive to "sp_executesql" the SQLServer abort the execution
> an cant capture the desire message.

This can only be done from the client application (in SQL 2000). In
SQL 2005 you can also use TRY/CATCH at the server side (with some
restrictions).

> There is way to control that in SQL server 2000 or other approach to
> do this. i am dealing with proprietary application and can't edit the
> source.

"If you can't edit it, rewrite it from scratch... or buy a better
one" :)

Razvan