ALTER PROCEDURE Sp_Login
(@username as nvarchar(100),@password as nvarchar(100))
AS
select count (*)from Tablename where Username=@username and
Password=@password


RETURN

Dixon wrote:
No SQL injection vulnerability here because no dynamic code is
executed.

There are other potential problems though. Do not use the prefix "sp_"
for non-system procedure names. That prefix is reserved for system
procs. If you use it for procs other than in Master then the proc may
fail to execute or may perform poorly due to recompilations.

Storing passwords in the database is an inherent security flaw and
ought to be avoided in any decent security model. Use integrated
security or hash the password rather than store it plain or encrypted.

Use EXISTS rather than SELECT COUNT(*) to validate the existence of
data - it tends to be much more efficient. Also, using output
parameters rather than returning a result set may work out more
efficienct too.

Hope this helps.

Thank u sooo very much it was reall helpful

Actually in our work place we try to sql inject each other's
application before v create the application as a whole..no one has
beeen able to do that with this code so i wanted to know if some one
here can do that......


but i still feel there may be some way of executing the code with sql
injection



for example,. if i execute the procedure with inputs

sp_Users_Login '''sa'' or Username like ''h%''','''sa'' or 1=1'

for the procedure

ALTER PROCEDURE Sp_Users_Login
(@username as nvarchar(100),@password as nvarchar(100))
AS
select count (*)from Users where Username=@username and
Password=@password

RETURN

wat will happen to the query inside it???

it should become

select count (*)from tab_pr_users where Username='sa' or Username like
'admin%' and  Password='sa' or 1=1

which will always be true if i hav a username called admin or
administrator

so ?????

y dont one SQL inject this code?

SQL Server doesn't just substitue the text (unless you use dynamic SQL, of course). Whatever you
send into that parameter, it will be searched for in the WHERE clause, and nothing else. So, in your
case, you will search for those with a username of:

'sa' or Username like 'h%'

Do not read any logic or quotes in above. If there is a row in the table with above value (text
string), you will get a hit. If not, no hit.

Try this code and you will perhaps see:

create table Users
(username varchar(40)
,password varchar(40))

insert into Users values('adminX', '')
insert into Users values('''sa'' or Username like ''h%''', '')
insert into Users values('sa', '')

select * from Users
GO
ALTER PROCEDURE Sp_Users_Login
(@username as nvarchar(100),@password as nvarchar(100))
AS
select * from Users where Username=@username
RETURN
GO

EXEC sp_Users_Login '''sa'' or Username like ''h%''','''sa'' or 1=1'


Show quoteHide quote

What happens if i dont use a stored procedure

wat happens if i just use the code as


select count(*) from Users where Username=txtloginname.text and
password=txtpwd.text

i guess in this context u can sql inject the code?

so can i conclude that if i use stored procedure without any dynamic
query building I'll be safe from sql injection?

You can't do that though can you.

You'd need to concatenate the string together

strSQL = "select count./.... where Username = '" + txtloginname.text etc....

Which you should NEVER do!

Tony.

Show quoteHide quote

as long as you do not have string concatenation when building the SQL on the
client or the database, then you are safe from SQL injection.  You can use
the parameter object with striaght SQL or stored procedures.

Check out these links for more info (all provided to me by other posters
here)

http://www.sqlservercentral.com/columnists/RDyess/sqlinjectionpart1.asp

http://www.sqlservercentral.com/columnists/chedgate/sqlinjection.asp

http://www.sqlservercentral.com/columnists/mcoles/updatedsqlinjection.asp

These should give you a clear understanding of SQL injection threats and how
to avoid them.


Show quoteHide quote

Dixon wrote:
One other thing. There is no injection vulnerability in the code you
posted but if you call this proc by constructing a string dynamically
in your application code then you may still be vulnerable. In ADO use
the parameters collection to pass parameters - don't construct dynamic
strings. Deny user permissions on tables and grant only execute
permission on procs.

Thats fine, I wouldn't prefix it sp_ though because it has special meaning.

I'd call it something like security_login becuase its part of your
'security' component and its the 'login' method.

Also, never never never never ever ever ever construct your SQL dynamically
in the application like this...

dim strSQL as string

strSQL = "exec ....@username='" & tbUSer.Text *& ...........

dbConn.Execute ( strSQL )

Always use the command object to build the statement, in VB.NET you'd do
something like this....

        Dim dbConn As New
SqlClient.SqlConnection(ConfigurationSettings.AppSettings("DBConnection"))
        dbConn.Open()

        Dim cmdSQL As SqlClient.SqlCommand
        Dim daSQL As New SqlDataAdapter
        Dim dsSQL As New DataSet

        '   Get Articles
        cmdSQL = New SqlCommand("ukug3_selGetForumList", dbConn)
        cmdSQL.CommandType = CommandType.StoredProcedure

        If Me.UserGUID <> "" Then cmdSQL.Parameters.Add(New
SqlParameter("@GUID", Me.UserGUID))
        cmdSQL.Parameters.Add(New SqlParameter("@is_all", IIf(isAll, "Y",
"N")))

        daSQL.SelectCommand = cmdSQL
        daSQL.Fill(dsSQL)

        datl.DataSource = dsSQL
        datl.DataBind()

        dbConn.Close()
        dbConn.Dispose()

Show quoteHide quote

Thank u sooo very much it was reall helpful

Actually in our work place we try to sql inject each other's
application before v create the application as a whole..no one has
beeen able to do that with this code so i wanted to know if some one
here can do that......


but i still feel there may be some way of executing the code with sql
injection



for example,. if i execute the procedure with inputs

sp_Users_Login '''sa'' or Username like ''h%''','''sa'' or 1=1'

for the procedure

ALTER PROCEDURE Sp_Users_Login
(@username as nvarchar(100),@password as nvarchar(100))
AS
select count (*)from Users where Username=@username and
Password=@password

RETURN

wat will happen to the query inside it???

it should become

select count (*)from tab_pr_users where Username='sa' or Username like
'admin%' and  Password='sa' or 1=1

which will always be true if i hav a username called admin or
administrator

so ?????

y dont one SQL inject this code?

It can never become the LIKE query because you are doing an =.

Whereas if you had written the query...

select count(*) from users where username like @username

The yes, you would have a problem.

Show quoteHide quote

Unicode encoding problem ?
Wrong Execution Plan
Stored procedure problem
DB Redesign - use junction/xref table?
Copying Records into a different table using triggers