Protectin Logon From SQL Injection - Users with apostrophe in logon name

I have a PHP page that takes login info and sends it to a login script.

I wanted to lock down the login script against SQL injections, so I have begun to use the mysql_real_escape_string() function on data input from that form.

Not sure if it's a server setting or what, but if a user has an apostrophe in their name like "Mike's Test", on submit it comes through as "Mike\'s Test"

Which of course, if I apply mysql_real_escape_string() to becomes "Mikes\\\'s Test" and then that fails in the user check query.

So what I'm doing is this:

$username = mysql_real_escape_string(stripslashes($_POST['username']));

This works - but is it still secure against SQL injection attack?
LVL 4
trippy1976Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

mayne171Commented:
you should narrow down your inputs by using preg_match (regex or regular expressions). Plus I don't know why you are allowing someone to login with apostrophes is beyond me. PLUS you are using mysql_real_escape_string innappropriately. It is supposed to go in a mysql statement. I have no idea what you are trying to do here but you need to take a look at the php manual pages because you are not doing this right IMO.

select * from table where username = " ' . mysql_real_escape_string($_POST['username']) . ' "

That is the general idea and I might be off somewhere I don't know...it's been awhile since I've done this. But I would recommend you condition a preg_match for the $_POST['username']) before you pass it in to the mysql statement. I take security seriously when I write my PHP/MYSQL code...
0
mayne171Commented:
Plus if you did it your way, you have to reverse the order you are doing things. You are saying "String the slashes first, that add the slashes back in" with mysql_real_escape_string. :-P
0
mayne171Commented:
*Strip the slashes first, then add the slashes back in"...whoa, where is the edit button or is that a "premium" feature also????
0
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Dave BaldwinFixer of ProblemsCommented:
On logins, I have both javascript and PHP that restrict the character set to allowable characters.  Quotes and semi-colons are not in the list.  Why both?  Because the 'good' guys will login thru the web page... and the 'bad' guys will try to bypass the web page and submit directly to the 'action' page.
0
trippy1976Author Commented:
mayne - seriously.  You registered here 4/23... don't start off assuming everyone here posting questions are fools.  I'm not looking for snarky editorial on what I'm doing from someone who admittedly hasn't done this in a long time and is therefore probably not the expert I seek.  I'm trying to find a definitive expert confirmation that stripping slashes and then re-applying mysql_real_escape_string() will still be safe.  

I'm doing a lot more checks than this, both client and server side.  We just upgraded the server and some of the environment settings are different so I'm having to change a little of my handling code to account for it (i.e. new server appears to have Magic Quotes on) It's a managed server and we don't have the ability to tweak the base server config for PHP.  I don't allow quotes, nor semi-colons, or colons or certain words.  But my users like apostrophes for things like "Jack's Mom", etc.  It's their online identity.  It makes my life harder but it makes my users happier and my site more successful.  So I live with the extra effort.

I think the answer is "yes this is still safe" but the question still stands and is unanswered.
0
Dave BaldwinFixer of ProblemsCommented:
It looks like what you're seeing is 'magic_quotes_gpc' is on on your server and this page, http://php.net/manual/en/function.addslashes.php, says that is the default and that it "essentially runs addslashes() on all GET, POST, and COOKIE data."   This page, http://en.wikipedia.org/wiki/SQL_injection, discusses SQL Injection in general and mentions a vulnerability in older versions of mysql_real_escape_string().  The MySQL manual says "Strictly speaking, MySQL requires only that backslash and the quote character used to quote the string in the query be escaped." although it does 'escape' more than addslashes does.  http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-string.html  It sounds to me like you're already doing the other things that make a difference.
0

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
trippy1976Author Commented:
Thanks Dave
0
mayne171Commented:
MYSQL_REAL_ESCAPE_STRING WILL NOT PROTECT YOU!

Not all SQL injections are stopped since many don't have comma's, quotes, etc. So mysql_real_escape_string is useless.

maybe you need to understand SQL Injection better and code appropriately!! I came here to help people...and I see that I came off as harsh but I don't need someone second guessing my intelligence that I don't know what I'm talking about...

Here is a website of SQL Injections.

http://old.justinshattuck.com/2007/01/18/mysql-injection-cheat-sheet/?akst_action=share-this

Also, future reference, your customers will typically ALWAYS choose convenience or security!
0
trippy1976Author Commented:
Hi mayne.  You actually second guessed your own intelligence, I quote:
"That is the general idea and I might be off somewhere I don't know...it's been awhile since I've done this."

How is someone supposed to accept that as definitive?  Seriously.  I don't doubt that at one point or another you were a hot shot with this, but the answer you provided was a little snarky and certainly didn't inspire confidence.  

I appreciate the reference and will digest that.

To my customers, I give both convenience and security.  You can do both.
0
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
PHP

From novice to tech pro — start learning today.