ADO query restricting to SELECT

Hi,
Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the
query as input from user. Run it using ADO connection. Display the result in
grid. However I want to allow only SELECT queries. Is there a property on
ADO which allows this? Otherwise I will have to do the parsing myself.

Thanks in advance.
Ajey

Hi

You will need to restrict the access with permissions at table level. If you
allow them to write their own queries instead of using a controlled query
builder or stored procedures then you will need to do your own parsing.

You should also read up on SQL Injection such as
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

John


"Ajey" wrote:

> Hi,
>      Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the
> query as input from user. Run it using ADO connection. Display the result 
in
> grid. However I want to allow only SELECT queries. Is there a property on
> ADO which allows this? Otherwise I will have to do the parsing myself.
>
> Thanks in advance.
>    Ajey
>
>
>
>

Hi,
By the post I understand that, u have a layee between the database and the
user.
The user need to send a select query and the result is displayed in the Grid
.

The users are presently having a flexibility to send the any kind of query.
If they send INSERT, UPDATE or DELETE, your data will be currupted, and you
wante to restrict that.

If my prediction was correct, what I suggest you is, to use a Stored
Procedure for this purpose or open the ADO with read only permissions.

I believe this answered your question. please revert back if u have any issu
es

thanks and regards
Chandra



"Ajey" wrote:

> Hi,
>      Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the
> query as input from user. Run it using ADO connection. Display the result 
in
> grid. However I want to allow only SELECT queries. Is there a property on
> ADO which allows this? Otherwise I will have to do the parsing myself.
>
> Thanks in advance.
>    Ajey
>
>
>
>

Thanks.
Yes, that's what i want to do.
And also I want to prevent sql-injection. Thanks John.
I want to restrict the query to SELECT. I even if the user has permissions
to modify the table I don't want him to alter it through the query.

How can I open ADO with read only permissons?

Thanks in advance.
Ajey

"Chandra" <Chandra@discussions.microsoft.com> wrote in message
news:CF97CE16-EFCF-4212-811C- A8C95E28D03E@microso
ft.com...
> Hi,
> By the post I understand that, u have a layee between the database and the
> user.
> The user need to send a select query and the result is displayed in the
Grid.
>
> The users are presently having a flexibility to send the any kind of
query.
> If they send INSERT, UPDATE or DELETE, your data will be currupted, and
you
> wante to restrict that.
>
> If my prediction was correct, what I suggest you is, to use a Stored
> Procedure for this purpose or open the ADO with read only permissions.
>
> I believe this answered your question. please revert back if u have any
 issues[color=darkred
]
>
> thanks and regards
> Chandra
>
>
>
> "Ajey" wrote:
> 
the 
result in 
on 

"Ajey" <ajey5@hotmail.com> wrote in message
news:OfmIdX8SFHA.2560@TK2MSFTNGP09.phx.gbl...
> Thanks.
> Yes, that's what i want to do.
> And also I want to prevent sql-injection. Thanks John.
> I want to restrict the query to SELECT. I even if the user has permissions
> to modify the table I don't want him to alter it through the query.
>
> How can I open ADO with read only permissons?

1. Set the Mode property of the underlying connection to adModeRead, and
2. Set the recordset.LockType property to adLockReadOnly


-Mark




> Thanks in advance.
>   Ajey
>
> "Chandra" <Chandra@discussions.microsoft.com> wrote in message
> news:CF97CE16-EFCF-4212-811C- A8C95E28D03E@microso
ft.com... 
> Grid. 
> query. 
> you 
> issues 
> the 
> result in 
> on 
>
>

You can do this while you are opening the connection itself.

For More info refer to:
http://www.codeguru.com/vb/gen/vb_d...icle.php/c5153/




"Ajey" wrote:

> Thanks.
> Yes, that's what i want to do.
> And also I want to prevent sql-injection. Thanks John.
> I want to restrict the query to SELECT. I even if the user has permissions
> to modify the table I don't want him to alter it through the query.
>
> How can I open ADO with read only permissons?
>
> Thanks in advance.
>    Ajey
>
> "Chandra" <Chandra@discussions.microsoft.com> wrote in message
> news:CF97CE16-EFCF-4212-811C- A8C95E28D03E@microso
ft.com... 
> Grid. 
> query. 
> you 
> issues 
> the 
> result in 
> on 
>
>
>

It's not working:
Following is the sample code:

----------------------------------------------------------------------------
-----------------------

g_objConn.Mode = adModeRead
g_objConn.Open strConn, , , 0
Debug.Print g_objConn.Mode

strQuery = "select * from sysobjects select * from sysindexes insert
into testtable values ('xyz')"

Set objRS = New Recordset
'Set objRS = g_objConn.Execute(strQuery)

objRS.Open strQuery, g_objConn, adOpenForwardOnly, adLockReadOnly,
adCmdText

----------------------------------------------------------------------------
-----------------------
After the Open on recordset the insert is always successful.

Thanks.
Ajey

"Mark J. McGinty" < mmcginty@spamfromyou
.com> wrote in message
news:OWodsc8SFHA.3980@TK2MSFTNGP12.phx.gbl...
>
> "Ajey" <ajey5@hotmail.com> wrote in message
> news:OfmIdX8SFHA.2560@TK2MSFTNGP09.phx.gbl... 
 permissions[color=da
rkred] 
>
> 1. Set the Mode property of the underlying connection to adModeRead, and
> 2. Set the recordset.LockType property to adLockReadOnly
>
>
> -Mark
>
>
>
> 
get 
 property[color=darkr
ed] 
myself. 
>
>

I am not using ADO.NET but it's a simple ADO application.

Thanks.
Ajey

"Chandra" <Chandra@discussions.microsoft.com> wrote in message
news:F419CECC-2DF9-4C52-953F- CEA0334B0336@microso
ft.com...
> You can do this while you are opening the connection itself.
>
> For More info refer to:
> http://www.codeguru.com/vb/gen/vb_d...icle.php/c5153/
>
>
>
>
> "Ajey" wrote:
> 
 permissions[color=da
rkred] 
the 
the 
and 
any 
get 
 property[color=darkr
ed] 
myself. 

Hi Ajey

Will this be of any help:

===========

Set conn = New ADODB.Connection
conn.Open "dns=<>"



Dim rs As ADODB.Recordset

' Open the table.
Set rs = New ADODB.Recordset
rs.Open Query, conn, adOpenDynamic, adLockReadOnly

===========

thanks and regards
Chandar


"Ajey" wrote:

> I am not using ADO.NET but it's a simple ADO application.
>
> Thanks.
>   Ajey
>
> "Chandra" <Chandra@discussions.microsoft.com> wrote in message
> news:F419CECC-2DF9-4C52-953F- CEA0334B0336@microso
ft.com... 
> permissions 
> the 
> the 
> and 
> any 
> get 
> property 
> myself. 
>
>
>

This seems to be doing the trick. I get following error when I try to
execute multiple queries:
sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single
select or a single stored procedure. : Microsoft OLE DB Provider for SQL
Server

But I don't want to use adOpenDynamic but adOpenForwardOnly cursor type.

At least this now blocks the user from executing multiple statement and so i
can check only the first token to be SELECT.
But can I achive same using adOpenForwardOnly cursor type.

Thanks.
Ajey

"Chandra" <Chandra@discussions.microsoft.com> wrote in message
news:34579EEE-DFDA-4768-9CD8- B5AAFEBE66B3@microso
ft.com...
> Hi Ajey
>
> Will this be of any help:
>
> ===========
>
> Set conn = New ADODB.Connection
>     conn.Open "dns=<>"
>
>
>
> Dim rs As ADODB.Recordset
>
>     ' Open the table.
>     Set rs = New ADODB.Recordset
>     rs.Open Query, conn, adOpenDynamic, adLockReadOnly
>
> ===========
>
> thanks and regards
> Chandar
>
>
> "Ajey" wrote:
> 
and 
in 
of 
 currupted,[color=dar
kred] 
 Stored[color=darkred
] 
permissions. 
have 
I 
the