Can one open a read-only connection or have a read-only command?

My application creates a SQL query depending on choice that user makes  on
the form. I am afraid that somwhere down the line somebody will try some sort
of SQL injection and be able to manipulate data. I would like to make the
connection read-only to prevent that.

Regards
Bojan

Hi Bojan,

Use an account that doesn't have permissions to modify data.
And why don't you use parametrised query as you should?

Show quoteHide quote

All users use some account to connect to the database. The authentication and
authorization are handled in the app.

The query builder is quite sofisticated and complicated and builds a SQL
statement depending on many inputs from the user.

Currently I check for presence of INSERT, UPDATE, DELETE etc. keywords.



Show quoteHide quote

But still, it should build parametrised statement. What's the problem?

On Sun, 19 Feb 2006 13:58:20 +0100, "Miha Markic [MVP C#]" <miha at
rthand com> wrote:

I agree with Miha.

It may be complicated, but if you are constructing a dynamic string
from user input fields you are less secure, even if you are parsing
for DDL expressions. 

Some injection attacks merely rely on creating errors in the SQL to
gain information such as server name, etc.  For example if I enter a
nonexistent column name in one of the fields that would make an
acceptable SQL statement, will your parsing catch it? 

I saw an example of this once. It was quite easy to do and used no DDL
expression syntax; just a word and a comma.  It was designed to get
the name of the server from the error message.  From there the example
showed the steps needed to compromise the server security.

A search of the Internet for "SQL Injection" will reveal sites giving
instructions for doing this.

Use parameters.  It may mean you have to use more logic in the
construction of your queries, but it will be much safer.  My guess is
you would rather spend a little more time writing code that having a
server compromised.

Here is an example:  Suppose you allow me to enter my account number
to get some sort of list and I enter LIKE %123% instead.  Will your
parser catch that?  Parameterized queries will.

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com

Thanks. I gather that there is no way of making connection read-only.

Show quoteHide quote

Of course there is and  in my opinion has Miha has answered that.

In a database that is only used to read, you can only set one user that has
only read rights. And remove the rights of the Administrator in that,
something the same as setting the MDF on a CD Rom.

Using only a read only connection in a program has no sense, it protects
nothing because the one who wants to hack does not need your program. He can
build his own.

Just my thought,

Cor

Using Typed Dataset with Winform vs ASP.NET
Timeout expired - on a simple SqlDataReader:Read() call
DAAB ExecuteReader method causes Exception
What is the quickest/most efficient way to insert 100,000 Rows
Advatages of Stored Procedures?