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.
AjeyHi
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 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@.microsoft.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
> 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
> >
> >
> >
> >|||"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@.microsoft.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
>> 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
>> >
>> >
>> >
>> >
>|||You can do this while you are opening the connection itself.
For More info refer to:
http://www.codeguru.com/vb/gen/vb_database/adonet/article.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@.microsoft.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
> >
> > 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
> > >
> > >
> > >
> > >
>
>|||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...
> > 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@.microsoft.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
> >>
> >> 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
> >> >
> >> >
> >> >
> >> >
> >
> >
>|||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@.microsoft.com...
> You can do this while you are opening the connection itself.
> For More info refer to:
> http://www.codeguru.com/vb/gen/vb_database/adonet/article.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@.microsoft.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
> > >
> > > 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
> > > >
> > > >
> > > >
> > > >
> >
> >
> >|||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@.microsoft.com...
> > You can do this while you are opening the connection itself.
> >
> > For More info refer to:
> > http://www.codeguru.com/vb/gen/vb_database/adonet/article.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@.microsoft.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
> > > >
> > > > 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
> > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||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@.microsoft.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:
> > 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@.microsoft.com...
> > > You can do this while you are opening the connection itself.
> > >
> > > For More info refer to:
> > > http://www.codeguru.com/vb/gen/vb_database/adonet/article.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@.microsoft.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
> > > > >
> > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||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@.microsoft.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:
> >
> > > 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@.microsoft.com...
> > > > You can do this while you are opening the connection itself.
> > > >
> > > > For More info refer to:
> > > > http://www.codeguru.com/vb/gen/vb_database/adonet/article.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@.microsoft.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
> > > > > >
> > > > > > 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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||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@.microsoft.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.
> 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@.microsoft.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:
> > >
> > > > 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@.microsoft.com...
> > > > > You can do this while you are opening the connection itself.
> > > > >
> > > > > For More info refer to:
> > > > >
http://www.codeguru.com/vb/gen/vb_database/adonet/article.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@.microsoft.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
> > > > > > >
> > > > > > > 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
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||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
>
>|||"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...
>> 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
>>
>|||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...
> > 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...
> >> 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
> >>
> >>
> >>
> >
> >
>|||"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...
>> "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...
>> >> 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
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||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...
> > 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...
> >>
> >> "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...
> >> >> 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
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>