duncanb7
asked on
Sql injection in php limit my password requirement on register page ?
Dear Experts,
Today, I review password requirement for my site register page. After understanding of
SQL injection matter, I doubt about what the requirement or validation password
in input password field for users before submitting the register form ?
Question-1
Do you think it is just let the requirement for password only with letters (upper or lowers cases or numbers) allowed that help to prevent any sql injection to mysql database
completely, (for example, such as password as '1' OR '1'='1' is not allowed) , Right ? Why I ask that , because I think if I am not allowing users or others
to input ' and '' or NULL or special characters to password requirement or validation on login page so that they are never able to do any SQL injection, Right ?
Quesiton-2
If what I said on question-1 is correct, why not let our users to input any character they like for his password including character of ' and '' if we can do hashing with md5() on those
password they input before running any mysql code in my register php page.
I mean hashing user passwords(any character they like) by md5() before submitting
to mysql database since the output of md5() is no any character of ' and " or NUL or
special character.
Anyway I guess that is not right, if right, why people suggest me to prevent SQL injection
by using PHP PDO or mysqli instead of mysql, and use prepare and bind_param
statements
I read a lot of articles from search in EE for "SQL injection", but it is really
hard to read them all for solving SQL injection completely just for setting password
requirement only.
Please advise
Duncan ( I was using mysql , NOW switch to mysqli and intend to use PHP PDO if need)
Today, I review password requirement for my site register page. After understanding of
SQL injection matter, I doubt about what the requirement or validation password
in input password field for users before submitting the register form ?
Question-1
Do you think it is just let the requirement for password only with letters (upper or lowers cases or numbers) allowed that help to prevent any sql injection to mysql database
completely, (for example, such as password as '1' OR '1'='1' is not allowed) , Right ? Why I ask that , because I think if I am not allowing users or others
to input ' and '' or NULL or special characters to password requirement or validation on login page so that they are never able to do any SQL injection, Right ?
Quesiton-2
If what I said on question-1 is correct, why not let our users to input any character they like for his password including character of ' and '' if we can do hashing with md5() on those
password they input before running any mysql code in my register php page.
I mean hashing user passwords(any character they like) by md5() before submitting
to mysql database since the output of md5() is no any character of ' and " or NUL or
special character.
Anyway I guess that is not right, if right, why people suggest me to prevent SQL injection
by using PHP PDO or mysqli instead of mysql, and use prepare and bind_param
statements
I read a lot of articles from search in EE for "SQL injection", but it is really
hard to read them all for solving SQL injection completely just for setting password
requirement only.
Please advise
Duncan ( I was using mysql , NOW switch to mysqli and intend to use PHP PDO if need)
ASKER
Dear Silghttwv,
thanks for your reviewing topic tag on my post.
Annd I understood the purpose of bind_param to prevent sql injection since it can reject any such thing ' or '' for '1' OR '1'='1' . If such easy, md5($_POST['password']) can also do that without bind_param 's help.
Duncan
thanks for your reviewing topic tag on my post.
Annd I understood the purpose of bind_param to prevent sql injection since it can reject any such thing ' or '' for '1' OR '1'='1' . If such easy, md5($_POST['password']) can also do that without bind_param 's help.
Duncan
sql injection occurs when you have code that constructs a sql statement using concatenation of user inputs and then executes that statement.
The easy and guaranteed way to prevent that is to NOT execute statements constructed from user input.
If you prompt for values, don't append/concatenate them. Use bind variables as suggested above.
So, create STATIC sql (i.e. statements that do not change) with a variable.
For example...
good - update USER_TABLE set password = :new_password WHERE userid = :id;
bad - update USER_TABLE set password = 'A634q!fh6' WHERE userid = 1234;
The exact syntax for binding will depend on the host language
The easy and guaranteed way to prevent that is to NOT execute statements constructed from user input.
If you prompt for values, don't append/concatenate them. Use bind variables as suggested above.
So, create STATIC sql (i.e. statements that do not change) with a variable.
For example...
good - update USER_TABLE set password = :new_password WHERE userid = :id;
bad - update USER_TABLE set password = 'A634q!fh6' WHERE userid = 1234;
The exact syntax for binding will depend on the host language
>>> If such easy, md5($_POST['password']) can also do that without bind_param 's help.
Yes, if you simply hash the password and store it, but never actually execute a sql statement with the raw text that will also prevent sql injection.
The key being do not execute anything that a user gets to type in.
That's it. SQL injection is one of the easiest security problems to fix.
Yes, if you simply hash the password and store it, but never actually execute a sql statement with the raw text that will also prevent sql injection.
The key being do not execute anything that a user gets to type in.
That's it. SQL injection is one of the easiest security problems to fix.
You 'can' probably get around the SQL injection by hashing the input before passing it off to the database.
You can also save on gas by pushing your car everywhere you want to go. It may work but is it really worth it?
The correct method to not only avoid SQL injection but also reduce Cross-Site Scripting issues (and other types of hacks) is to use bind variables.
Just use them.
You can also save on gas by pushing your car everywhere you want to go. It may work but is it really worth it?
The correct method to not only avoid SQL injection but also reduce Cross-Site Scripting issues (and other types of hacks) is to use bind variables.
Just use them.
ASKER
I think you don't understand my question,
Could we do that
update USER_TABLE set password =md5( "A634q!fh6") WHERE userid =1234;
?
Could we do that
update USER_TABLE set password =md5( "A634q!fh6") WHERE userid =1234;
?
ASKER
slightwv, XSS can be solved by latest browser with setting cookie at
http only.
http only.
>>Could we do that
Not if A634q!fh6 was typed in by the user and you used string concatenation to build the update statement.
Not if A634q!fh6 was typed in by the user and you used string concatenation to build the update statement.
update USER_TABLE set password =md5( "A634q!fh6") WHERE userid =1234;
this still qualifies as "bad" because you are executing text the user has typed in.
better is....
update USER_TABLE set password =md5( :newpassword ) WHERE userid =:id;
or store the md5 hashed value in a variable then use the variable version posted previously
:newpassord being the hashed value rather than the raw text
this still qualifies as "bad" because you are executing text the user has typed in.
better is....
update USER_TABLE set password =md5( :newpassword ) WHERE userid =:id;
or store the md5 hashed value in a variable then use the variable version posted previously
:newpassord being the hashed value rather than the raw text
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
why it is bad, the output of md5( "A634q!fh6") is no any ' ,'', NUL,or special character those
character is sensitive to SQL injection
character is sensitive to SQL injection
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cristian Moecke, you are right. But If use md5() to avoid sql injection, so I don't need to use
mysqli or PHP PDO to replace my old mysql php code. Right, at least concept even I know mysql will be deprecated soon ?
mysqli or PHP PDO to replace my old mysql php code. Right, at least concept even I know mysql will be deprecated soon ?
it is bad because while you fix this sql injection, you will leave many other sql injection points open on your code. If you forget to check and fix by this kind of workaround any sql data that comes directly from user input, you will have a sql injection point. Its bad because variable binding is THE solution for this problem, and using it will make you stop needind to worry about every point that may need to have some special treatment because of SQL injection. Easier to read and mantain code, etc, etc, etc.
Really no need to reinvent the wheel here.
But, having that said, its also a good practice to store the password hashed, ok? So just do it too, but not for preventing SQLi, but for preventing that password get stealed if someone gets the DB data. Use salted hashes!
Really no need to reinvent the wheel here.
But, having that said, its also a good practice to store the password hashed, ok? So just do it too, but not for preventing SQLi, but for preventing that password get stealed if someone gets the DB data. Use salted hashes!
Oh and please notice as I mentioned that MD5 inside the sql query will NOT fix the SQLi!!!
You need to have it pre-computed firts!
sdstuber showed an example of another SQL injection query that would compromise the query if you use MD5 from the sql system!
You need to have it pre-computed firts!
sdstuber showed an example of another SQL injection query that would compromise the query if you use MD5 from the sql system!
ASKER
sdstuber, now I understand what you said, you right, the user can type in like this
12) OR '1'='1' so md5(12) or '1' ='1') where ... just idea not exactly;
So last question if I use bind_param , the password input requirement could be
any character even ', ''% ,/,\ , NUL , and no need to limit user's input they like , RIght ?
12) OR '1'='1' so md5(12) or '1' ='1') where ... just idea not exactly;
So last question if I use bind_param , the password input requirement could be
any character even ', ''% ,/,\ , NUL , and no need to limit user's input they like , RIght ?
Exactly. Because then the SQL system will consider anything as value of a variable, not as part of the query.
>>>> So last question if I use bind_param , the password input requirement could be
any character even ', ''% ,/,\ , NUL , and no need to limit user's input they like , RIght ?
yes - if you use a bind variable then you completely eliminate sql injection and they can use any characters they want.
any character even ', ''% ,/,\ , NUL , and no need to limit user's input they like , RIght ?
yes - if you use a bind variable then you completely eliminate sql injection and they can use any characters they want.
ASKER
slightwv, agree my & Cristian Moecke's last post ?
User could input any kind of character for password(even ','',/,\,%) when they register if
I am using mysqli with bind_param and prepare, Right ?
Duncan
User could input any kind of character for password(even ','',/,\,%) when they register if
I am using mysqli with bind_param and prepare, Right ?
Duncan
>>Right ?
All three participating Experts have pretty much already said this. That is what bind variables do.
All three participating Experts have pretty much already said this. That is what bind variables do.
ASKER
Thanks for all your reply.
Have a nice day
Duncan
Have a nice day
Duncan
You should also hash passwords when stored anywhere.