[PHP][MSSQL] Secure Login Script Help Needed!

Joined
Jul 1, 2005
Messages
77
Reaction score
0
I am using PHP (running latest xampp) & MSSQL 2000. This is for an MU Online website. I just started learning SQL & PHP yesterday lol.

I understand how SQL injection works & what it does. The Problem: I am going to use a function to clean form input during account registration & account login to stop injection. I understand for MySQL there is mysql_real_escape_string(), but no equivalent for MSSQL.
So a few questions:

1) What can I use to protect my form inputs instead of mysql_escape_string(). How can I use it?
2) What can I do to make sure only A-Z, a-z, 0-9 characters are being used in my fields. I want anything else to fail before any query is even run.
3) I've read online that stored procedures can be used in MSSQL to prevent SQL injection. Can someone please explain how to protect this way as well?

Thanks in advance for help!
 
The only *real* good way to stop SQL Injection attacks is to just parameterize your SQL Queries that are dynamically built.

I don't know PHP, but here's the basic premise...

GOOD: Select * from people where firstname = @firstname
BAD: Select * from people where firstname = '(try-to-sanitize)'
INSTA-PWNED: Select * from people where firstname = 'user input'

...With parameters (@firstname), you just add a parameter to the query and define the parameters before executing the statement
in C# it's SqlCommand.Parameters.Add("firstname", SqlDbType.type).Value = string;

When you do it this way (parameters), there's zero chance of SQL Injection attacks.
 
works no matter what database you use
PHP:
$error = array();

if(!alnum($varLogin)) {
   $error['foo'] = "bar has to be alphanumeric";
}

if(!alnum($varPass)) {
   $error['foo2'] = "bar2 has to be alphanumeric";
}


if(empty($error)) {
//perform shits
}

else {
   print_r($error);
}
 
Blacklisting is a horrible idea for starters lol in numerous ways
and yes MSSQL uses stored procedures which is essentially an extension of what you can do web server-wise.

MSSQL is horrible with injections simply because it allows for the scripter to do whatever they wish, which in-turn allows for people to inject as they wish as well =/
Here yah go:
http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/
That pages will help you with preventing essentially all injections.
 
MSSQL is not horrible with injections lol

It's got nothing to do with it being MSSQL, or PostgreSQL, or MySQL, or anything else. It's the programming done to construct queries. Geez lol
 
Blacklisting is a horrible idea for starters lol in numerous ways
well i also want to blacklist characters that way i dont end up with any funky account names or usernames :)


someone sent me this from the MU Online release section:
http://forum.ragezone.com/f196/guide-anti-sql-inject-with-0-8-and-others-webs-484364/

that script is pretty simple, seems really secure, and it even logs whether or not there are sql injection attempts. if i use that in conjunction with by .htaccess deny, would it be enough security?

Also, I plan on using session to carry info like user login name & password. before doing any INSERT queries, i plan on checking the user login name w/ the database. then checking the password w/ the password existing in row i pulled from database. is this a good, bad, or unnecessary idea?
 
Last edited:
Also, I plan on using session to carry info like user login name & password. before doing any INSERT queries, i plan on checking the user login name w/ the database. then checking the password w/ the password existing in row i pulled from database. is this a good, bad, or unnecessary idea?

I usually simply do a select on username AND password - if you get a result, it matches both. Saves out a string comparison, though these are so fast these days that it doesn't really matter much anyhow. Also remember that it's good practice to store IP information in the session to prevent session theft (which is not really that big a problem, but you never know).

Stored procedures will become more common I hope now that MySQL is finally starting to support them to some extend. Not only are they more secure (they work pretty much like onemorejosh posted: you define parameters to execute on an already existing query), they can generally also be optimized by your DBM making them somewhat faster. You can set parameters for an MSSQL stored procedure using mssql_bind.
 
I usually simply do a select on username AND password - if you get a result, it matches both. Saves out a string comparison, though these are so fast these days that it doesn't really matter much anyhow. Also remember that it's good practice to store IP information in the session to prevent session theft (which is not really that big a problem, but you never know).

Stored procedures will become more common I hope now that MySQL is finally starting to support them to some extend. Not only are they more secure (they work pretty much like onemorejosh posted: you define parameters to execute on an already existing query), they can generally also be optimized by your DBM making them somewhat faster. You can set parameters for an MSSQL stored procedure using mssql_bind.

good point on using AND, didn't even think to apply that here. but like I said i'm new to SQL & PHP...but i have programmed VB.net before. i'll look into the mssql_bind & read up on the documentation. i understood onemorejosh's post, I just wasnt sure how to do it lol
 
one little note about stored procedures

If you're going to execute them as a query, that does NOTHING to stop sql injections. For example:

Insta-hacked: "Exec mystoredprocedure "+textbox1.text
Good: "Exec mystoredprocedure @userstext"

You still need to use parameters. And don't get confused with parameters passed to the stored procedure... We're talking about parameters in the query itself, using @param instead of the user's text.

FYI:

Here's how SQL Injection works:

http://xkcd.com/327/
 
Last edited by a moderator:
Back