Authentication Query

When a user logs in to a web app, how does the SQL query hit the Database to see if the account is valid and allow/reject login? I am trying to get my head around SQL injection but I've only used SQL (which I think was T-SQL) in data analysis before which is typically SELECT statements. If you could provide a sample SQL statement that is typical when a user POSTS their username/password pair that will query the Database to see if the account is valid that would be most helpful.

LVL 4
pma111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

devlab2012Commented:

The basic query will be like this:

select * from User where UserID = 'user_id' and password = 'password'

If I am using .NET, i will write the query like this:
select * from user where userid = @userid and password = @password

and then add the values of the parameters @userid and @password as entered by the user.
devlab2012Commented:
How SQL injection can be used to attack in this scenario:

To pass the values entered by users, the query may be written as: (this approach is commonly used in ASP, PHP etc)

"select * from user where userid =' " + vUserID + " ' and password = ' " + vPassword + " ' "

where vUserID and vPassword contains the values entered by the user. Now suppose the user enters "admin" and "pass" as user id and password the above query will become:
"select * from user where userid = 'admin' and password = 'pass' "
This will work perfectly fine.

But, to inject sql, user can enter user id like this (in case of Microsoft SQL Server as database):

" admin'; truncate table user; --" and "pass" as password. Now the query will become:

"select * from user where userid = 'admin'; truncate table user; --' and password = 'pass' "

Now there are two queries - one is SELECT, other is TRUNCATE TABLE and the comment (anything after "--" is a comment in sql server). This is a simple example of SQL Injection attack.


pma111Author Commented:
Thanks for the info - out of interest, how on earth does the attacker see what they are entering in a password field which seems to be where you inject the extra SQL. Password fields are normally masked by stars or asterics so that must hamper the attacker. As I understand if you POST (HTML) the username/password its hidden from the URL, so they cant amend the value their either.

Also, the counter attacks for injection seem to be limiting which characters are set on password field. So for example not allowing or accepting ' . Does this have an impact on the password complexity features of an application, as we normally ask for 10 minimum and 1 from upper, lower, numbers, specials. So if you dont allow ' then you cant allow specials, and thus password complexity suffers?

I dont suppose you have a sample .net login code for my research? Or could you point me in the right direction if you are a coder as you'll likely no good sites that have sample login/search/logout sample asp/php
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

devlab2012Commented:
Its not clear that what you are trying to say. I gave the example by supposing that you are new to sql and hacking. If you have some experience with sql injection or hacking then I am sorry to say that the above example was not for you.
Regarding your point that password fields are masked by stars, then that is only for the user interface. If the user is entering the password and trying to inject sql, he knows what he is entering!!! Masking by stars or asterics does not mean that the user does not know what he has entered.

If you need the sample code in .NET, the following snippet may help:

protected void btnLogin_Click(Object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("your_connection_string");
SqlCommand cmd = new SqlCommand("select * from User where username = @username and password = @password", conn);
conn.open();
cmd.Parameters.Add("@username", SqlDbType.Varchar).Value = txtUserName.Text;
cmd.Parameters.Add("@password", SqlDbType.Varchar).Value = txtPassowrd.Text;
DataReader dr = cmd.ExecuteReader();
if dr.HasRows
{
//login is successful. You can read other data about the user from DataReader like type of user. Then redirect accordingly.
Session["UserID"] = dr["UserID].ToString();
Response.Redirect("MemberMain.aspx");
}
else
{
lblMessage.Text = "You entered wrong user name or password.";
}
}
pma111Author Commented:
It was a great example you gave, I just wondered if you could help with the last part of the question.

If we restrict ' from either username or password we have limited available password complexity as not all special characters can be used? Or am I wrong?

If you audit the System as a whole, where do you typically look for SQLi vulnerabilities, is it the asp.net code or do you go directly to the Database and review that first?
devlab2012Commented:
In the above example, I am using parameterized queries. This eliminates the possibility of SQL injection.

In instead of using the parameterized query, I concatenate the user name and password with the query itself then we have to take care of special characters. In parameterized queries, use of any special characters will be considered a part of the user name or password and will not affect our query. So there is no need to restrict the use of single quote (') or any other special character.
pma111Author Commented:
Excuse my ignornace but does parameterized query = stored procedure? Is the use of parameterized query common, or are there examples when developers cant use parameterized queries?

Thanks for all your help
devlab2012Commented:
Parameterized query is not a stored procedure, but like a normal query. The use of parameterized queries is quite common. In .NET world we always either use parameterized queries or stored procedures. Writing a stored procedure for each kind of query that we need in a project is always not worth the effort (different developers will have different opinions and preferences about this), so the use of parameterized queries is not worth the effort.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
devlab2012Commented:
sorry for the typing mistake in last line of above comment. I was supposed to type "so the use of parameterized queries is quite common".
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.