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 ?

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 ?

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

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)
LVL 13
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.

slightwv (䄆 Netminder) Commented:
Bind variables will prevent SQL Injection.

You should also hash passwords when stored anywhere.
duncanb7Author Commented:
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.

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
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

>>> 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.
slightwv (䄆 Netminder) Commented:
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.
duncanb7Author Commented:
I think you don't understand  my question,

Could we do that

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

duncanb7Author Commented:
slightwv, XSS can be solved by latest browser with setting cookie at
http only.
slightwv (䄆 Netminder) Commented:
>>Could we do that

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
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.
duncanb7Author Commented:
why it is bad, the output of md5( "A634q!fh6") is no any ' ,'', NUL,or special character  those
character is sensitive to SQL injection
>>>  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.

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
slightwv (䄆 Netminder) Commented:
>>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.
duncanb7Author Commented:
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 ?
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!
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!
duncanb7Author Commented:
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 ?
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.
duncanb7Author Commented:
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 ?

slightwv (䄆 Netminder) Commented:
>>Right ?

All three participating Experts have pretty much already said this.  That is what bind variables do.
duncanb7Author Commented:
Thanks for all your reply.
Have a nice day
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

From novice to tech pro — start learning today.