Solved

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

Posted on 2014-01-10
23
902 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 5
  • +1
23 Comments
 
LVL 77

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 74

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 74

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 77

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 77

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 74

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
 
LVL 74

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 77

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 74

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 77

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

751 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