PHP SQL Injection Help! Urgent!

Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.

Moderator: General Moderators

Post Reply
aashish_2025
Forum Newbie
Posts: 7
Joined: Mon Jun 14, 2010 2:37 am

PHP SQL Injection Help! Urgent!

Post by aashish_2025 »

I've developed a sort of a web application that allows users to register themselves and then post articles. And, since I need to accept data from users and then interact with database, sql injection bothers me a lot.

First of all, let me tell what I've done for security right now.

I've escaped all inputs. But, to remain further safe, I allow only certain characters in a field. A server-side validation checks that. The following is a list of fields and what characters I accept for example.

During registration:
  • Name(alphabets and period)
  • Username(alphanumeric characters, underscore and period)
  • Password(alphanumeric characters, underscore and period)
  • Phone(numbers only)
  • Address(alpha-numeric, comma and period)
See how funny thing I've done. I mean I allow only alphanumeric, underscore and period in password making the passwords insecure by myself. :banghead:

But, I can't find anything else to secure my application. I know password isn't an issue because irrespective of the characters entered, they will be hashed. So, I guess, I can accept any character. But, the main issue is for other fields. All these ridiculous validation, I guess, irritates my users to every bit.

But, the saddest thing starts when a user wants to post an article. For posting an article, there are 4 fields that need to be filled; Title, Summary, Article and Tags.

Just imagine how annoying it must be when the Title allows nothing but alphabets, space and numbers(!,? also). And, the field "Summary" also accepts the same as the Title. And, all this simply so that I can secure the web application! Ridiculous, isn't it?

For the article field, I've used ckeditor(http://ckeditor.com/). So, I haven't done anything as I guess it does the escaping, converting to HTMLchars and all by itself.

Now, the article field accepts Unicode characters(I mean characters in other scripts) but the title and summary doesn't. And, most of my users don't publish in English. So, in a way, the application is being useless.

So, this is basically an issue of making it user-friendly vs. security. I know there are other much better ways of ensuring security than what I've done and that's what I wanna ask you people.

I was thinking that since I don't "Select" any data on any of the above parameters(title, tags, summary, address), I can simply escape the characters and convert them to HTMLchars if they are printed on a page somewhere. But I'm not so sure if that much will be secure enough. Will it?

So, this is all! I just need something stronger to prevent SQL injection(and XSS). I know that using Prepared Statements or PDO is the best thing to do. But, I'm an extremely new beginner in all this and I can't find any simple tutorial. If you know of any, it'd be really kind of you to tell me.

Yeah! That's all! And, one more thing, most of my users don't seem to activate their account(through the email the application sends). But, I guess, that's vital to verify the email. So, is there anything that can be done? Any better idea?

P.S.: I use SQL and not SQLi. And, if I need to convert my app. from sql to sqli, can I use some sort of scripts? I came across one at http://dev.mysql.com/get/Downloads/Cont ... /from/pick.

Sorry for my poor English and hope someone will help!
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: PHP SQL Injection Help! Urgent!

Post by social_experiment »

A tutorial on prepared statements
http://mattbango.com/notebook/web-devel ... nd-mysqli/

If you are properly employing mysql_real_escape_string() and / or using prepared statements you shouldn't have a problem allowing characters such as ' or " in the fields.

A point you might want to look at is only allowing users access related to their needs; by this i mean if the users can only post articles, delete items from the database or update, don't use an sql password / username combination that has super-user access. In a sense you are controlling the amount damage that occurs if sql injection happens.
aashish_2025 wrote:And, one more thing, most of my users don't seem to activate their account(through the email the application sends). But, I guess, that's vital to verify the email. So, is there anything that can be done? Any better idea?
I'm not sure what you mean here: a) users register but do not activate accounts or b) users register but activate accounts through an alternate method?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: PHP SQL Injection Help! Urgent!

Post by flying_circus »

social_experiment wrote:If you are properly employing mysql_real_escape_string() and / or using prepared statements you shouldn't have a problem allowing characters such as ' or " in the fields.
As Social Experiment said, if you are escaping data correctly, we dont care what the user enters in the field. Let them enter whatever they want!

What matters is how YOU handle the data in your application. If you are using MySQL then there are some good escaping functions, but be sure that when you escape data, no other operations are called on the data before entry into the database. I really like to use the sprintf() function for escaping data before insertion into SQL queries.

The second part of the equation which I didnt see mentioned was encoding output. If someone enters <script type="text/javascript">alert('XSS!');</script> in the title field, you need to make sure the data is encoded before you put it back into your html. htmlspecialchars($myVar, ENT_QUOTES) works really well for this, otherwise your article viewers will get an alert box when they visit your article.

It's not as tricky or difficult as it seems, notice how this website does it :)
User avatar
Mordred
DevNet Resident
Posts: 1579
Joined: Sun Sep 03, 2006 5:19 am
Location: Sofia, Bulgaria

Re: PHP SQL Injection Help! Urgent!

Post by Mordred »

Depending on how badly the code is written, you can have SQL injection with only alphanumerics, no need for a quote.
Read my article linked in my signature on details.
Post Reply