Solved

Sql injection in php limit my password requirement on register page ?

Posted on 2014-01-10
23
812 Views
Last Modified: 2014-01-10
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)
0
Comment
Question by:duncanb7
  • 8
  • 5
  • 5
  • +1
23 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39771634
Bind variables will prevent SQL Injection.

You should also hash passwords when stored anywhere.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 39771651
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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39771655
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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39771660
>>> 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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39771667
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.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 39771672
I think you don't understand  my question,

Could we do that

 update USER_TABLE set password =md5( "A634q!fh6") WHERE userid =1234;

?
0
 
LVL 13

Author Comment

by:duncanb7
ID: 39771679
slightwv, XSS can be solved by latest browser with setting cookie at
http only.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39771680
>>Could we do that

Not if A634q!fh6 was typed in by the user and you used string concatenation to build the update statement.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39771692
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
0
 
LVL 3

Assisted Solution

by:cristiantm
cristiantm earned 100 total points
ID: 39771701
I think you did not understood the answers :)

The thing is, if you use the bind variables approach, you will not need to worry about the password content anymore. It will accept ' or ''  1 passwords without leading to sql injections, because the query will not consider the ' and " as delimiters but will only consider them as values of the variable. Therefore, no SQL injection from those.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 39771708
why it is bad, the output of md5( "A634q!fh6") is no any ' ,'', NUL,or special character  those
character is sensitive to SQL injection
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 73

Accepted Solution

by:
sdstuber earned 300 total points
ID: 39771731
>>>  why it is bad, the output of md5( "A634q!fh6")

that's because A634q!fh6 was typed in by a friendly user.

evil user types in something like this...


A");  --

So your sql statement is now...


update USER_TABLE set password =md5( "A");  --") WHERE userid =1234;

when you execute that, you just set every user's password to "A".

That's just one example.  You can get more creative.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 39771732
>>why it is bad,

It has already been mentioned several times:  If you use string concatenation to build executable code you open yourself up to issues.

Bind variables eliminate the threat.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 39771734
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 ?
0
 
LVL 3

Expert Comment

by:cristiantm
ID: 39771740
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!
0
 
LVL 3

Expert Comment

by:cristiantm
ID: 39771746
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!
0
 
LVL 13

Author Comment

by:duncanb7
ID: 39771755
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 ?
0
 
LVL 3

Expert Comment

by:cristiantm
ID: 39771760
Exactly. Because then the SQL system will consider anything as value of a variable, not as part of the query.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39771764
>>>> 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.
0
 
LVL 13

Author Comment

by:duncanb7
ID: 39771776
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39771779
>>Right ?

All three participating Experts have pretty much already said this.  That is what bind variables do.
0
 
LVL 13

Author Closing Comment

by:duncanb7
ID: 39771792
Thanks for all your reply.
Have a nice day
Duncan
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now