|
Home > Archive > MS SQL Server > April 2005 > ADO query restricting to SELECT
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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
| |
| John Bell 2005-04-28, 3:23 am |
| 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
>
>
>
>
| |
| Chandra 2005-04-28, 3:23 am |
| 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
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[color=darkred]
result in[color=darkred]
on[color=darkred]
| |
| Mark J. McGinty 2005-04-28, 3:23 am |
|
"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
>
>
| |
| Chandra 2005-04-28, 3:23 am |
| 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[color=darkred]
property[color=darkr
ed]
myself.[color=darkred]
>
>
| |
|
| 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...[color=darkred]
> 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[color=darkred]
the[color=darkred]
and[color=darkred]
any[color=darkred]
get[color=darkred]
property[color=darkr
ed]
myself.[color=darkred]
| |
| Chandra 2005-04-28, 7:23 am |
| 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...[color=darkred]
> 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[color=darkred]
in[color=darkred]
of[color=darkred]
currupted,[color=dar
kred]
Stored[color=darkred
]
permissions.[color=darkred]
have[color=darkred]
I[color=darkred]
the[color=darkred]
| |
| Chandra 2005-04-28, 7:23 am |
| Hi Ajey
Good to know that ur problem is getting solved. adLockReadOnly will prevent
the users from using INSERT, DELETE and UPDATE. The cursor type is optional.
You can ignore it and procede further.
thanks and regards
Chandra
"Ajey" wrote:
> 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...
> and
> in
> of
> currupted,
> Stored
> permissions.
> have
> I
> the
>
>
>
| |
|
| The error comes only when I specify the cusrosor type as adOpenDynamic and
not when adOpenForwardOnly
"Chandra" <Chandra@discussions.microsoft.com> wrote in message
news:7F7945AF-AACD-4545-B5F2- 1ACE9D3FD1D7@microso
ft.com...
> Hi Ajey
> Good to know that ur problem is getting solved. adLockReadOnly will
prevent
> the users from using INSERT, DELETE and UPDATE. The cursor type is
optional.[color=darkred]
> You can ignore it and procede further.
>
> thanks and regards
> Chandra
>
>
>
>
> "Ajey" wrote:
>
single[color=darkred
]
so i[color=darkred]
http://www.codeguru.com/vb/gen/vb_d...icle.php/c5153/[color=darkred]
query.[color=darkred]
database[color=darkr
ed]
displayed[color=dark
red]
kind[color=darkred]
ADO?[color=darkred]
Display[color=darkre
d]
a[color=darkred]
parsing[color=darkre
d]
| |
|
| Also, I created a stored procedure sp_my_sp2 which raises an error on
execution.
When I try to run multiple queries as:
sp_my_sp2 select * from sysobjects
I can verify that the stored proc as well as the SELECT query gets executed.
But for the following:
select * from sysobjects sp_my_sp2
only the SELECT query gets executed.
What's the behavior for multiple queries in SQL Server?
Thanks in advance.
Ajey
"Ajey" <ajey5@hotmail.com> wrote in message
news:e8bxOD8SFHA.612@TK2MSFTNGP12.phx.gbl...
> 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
>
>
>
| |
| Mark J. McGinty 2005-04-28, 9:23 am |
|
"Ajey" <ajey5@hotmail.com> wrote in message
news:%23Dd%23xs$SFHA
.2520@TK2MSFTNGP09.phx.gbl...
> Also, I created a stored procedure sp_my_sp2 which raises an error on
> execution.
> When I try to run multiple queries as:
> sp_my_sp2 select * from sysobjects
> I can verify that the stored proc as well as the SELECT query gets
> executed.
>
> But for the following:
> select * from sysobjects sp_my_sp2
select * from sysobjects; exec sp_my_sp2
-Mark
> only the SELECT query gets executed.
>
> What's the behavior for multiple queries in SQL Server?
>
> Thanks in advance.
> Ajey
>
>
> "Ajey" <ajey5@hotmail.com> wrote in message
> news:e8bxOD8SFHA.612@TK2MSFTNGP12.phx.gbl...
> in
>
>
| |
|
| That helps to execute the stored procedure as well
I wanted to make sure that user does not enter any other statement other
than SELECT. I can put restriction on having keywords INSERT, UPDATE,
DELETE, INTO, EXEC, EXECUTE in the query string but what about stored
procedure given as mentioned below. With the execution i verified that in
this case the stored procedure does not get executed. But is this behavior
documented?
"Mark J. McGinty" < mmcginty@spamfromyou
.com> wrote in message
news:2j6ce.1$ZN.0@fed1read07...
>
> "Ajey" <ajey5@hotmail.com> wrote in message
> news:%23Dd%23xs$SFHA
.2520@TK2MSFTNGP09.phx.gbl...
>
>
> select * from sysobjects; exec sp_my_sp2
>
>
> -Mark
>
>
>
>
>
result[color=darkred
]
on[color=darkred]
>
>
| |
| Mark J. McGinty 2005-04-28, 11:23 am |
|
"Ajey" <ajey5@hotmail.com> wrote in message
news:uAiO1EATFHA.2996@TK2MSFTNGP15.phx.gbl...
> That helps to execute the stored procedure as well
> I wanted to make sure that user does not enter any other statement other
> than SELECT. I can put restriction on having keywords INSERT, UPDATE,
> DELETE, INTO, EXEC, EXECUTE in the query string but what about stored
> procedure given as mentioned below. With the execution i verified that in
> this case the stored procedure does not get executed. But is this behavior
> documented?
Not sure if it's documented, but it has been that way forever (given that
SQL 6.5 marked the beginning of time.)
Just the stored procedure name (with args if any) alone will work for a
single statement, but you must use exec for each statement in a batch.
-Mark
> "Mark J. McGinty" < mmcginty@spamfromyou
.com> wrote in message
> news:2j6ce.1$ZN.0@fed1read07...
> result
> on
>
>
| |
|
| Thank Mark.
I even verified with the query execution plan to make sure that the stored
proc does not get executed. But then why no error is shown for the stored
proc name.
"Mark J. McGinty" < mmcginty@spamfromyou
.com> wrote in message
news:2g8ce.17$ZN.14@fed1read07...
>
> "Ajey" <ajey5@hotmail.com> wrote in message
> news:uAiO1EATFHA.2996@TK2MSFTNGP15.phx.gbl...
in[color=darkred]
behavior[color=darkr
ed]
>
> Not sure if it's documented, but it has been that way forever (given that
> SQL 6.5 marked the beginning of time.)
>
> Just the stored procedure name (with args if any) alone will work for a
> single statement, but you must use exec for each statement in a batch.
>
> -Mark
>
>
>
>
get[color=darkred]
property[color=darkr
ed]
myself.[color=darkred]
>
>
|
|
|
|
|