asked on

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?

mayne171

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

mayne171

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

mayne171

*Strip the slashes first, then add the slashes back in"...whoa, where is the edit button or is that a "premium" feature also????

Dave Baldwin

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.

trippy1976

ASKER

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.

ASKER CERTIFIED SOLUTION

Dave Baldwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

trippy1976

ASKER

Thanks Dave

mayne171

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!

trippy1976

ASKER

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.