Sorry your browser is not supported!

You are using an outdated browser that does not support modern web technologies, in order to use this site please update to a new browser.

Browsers supported include Chrome, FireFox, Safari, Opera, Internet Explorer 10+ or Microsoft Edge.

Geek Culture / PHP and MySQL security, and website design...

Author
Message
Izzy545
20
Years of Service
User Offline
Joined: 18th Feb 2004
Location:
Posted: 18th May 2008 22:18
Hi everyone, I'm making a website that uses a MySQL database and PHP to display user submissions. If I'm only using the PHP form to insert into the MySQL database what kind of concerns should I be looking at in terms of SQL injections?

Also, I find it odd that I have to put my password right on the PHP file I'm using to access my database, any safety concerns there?

Last thing: Just wondering if you guys could comment on the current design of my site, things I could do to improve... I haven't done much in the way of website design, so any comments are appreciated.

http://izzy545.my5gb.com/Index.php

P.S. Feel free to use the submit form to add some more entries of whatever random junk you want, I want to see how well that's working and such.

Omega gamer 89
17
Years of Service
User Offline
Joined: 10th Sep 2007
Location: Pittsburgh, PA
Posted: 18th May 2008 22:33
Okay, in all honesty? Its bad. There is so little color, so little contrast, it makes things sort of hard to see. there is far too much empty space. A bit of whitespace is always good, but you have a huge excess of it. Add a little color, but don't go crazy with it. make better use of your page space, and liven things up a little. Add links to related sites and maybe add in an image or two, just to add some variety. I submitted a poem with some constructive criticism. Its a haiku.

...but I am the ferret king!!!! BWAHAHAHAHAHAHA!!!! tremble before my ferret minions!

GatorHex
19
Years of Service
User Offline
Joined: 5th Apr 2005
Location: Gunchester, UK
Posted: 18th May 2008 22:40 Edited at: 18th May 2008 22:42
Learn to program in Perl coz PHP is a hackers dream.

Never upload to your PHP,CGI-BIN,Perl directories and make sure permissions there are execute only. Don't allow write access to your htdocs root folder and access there should be read only.

Validate and remove any back refrences from input such as "../" and code injection stings ";" and kill executable filenames such as ".htm" ".html" ".bat" ".com" ".exe" ".php". I'd probably just restrict uploads to know good extensions like .arj .zip .jpg .bmp and dump everything else.

Make sure all your strings stay strings eg:
'$1+$2' = string saying $1+$2
rather than
"$1+$2" = math result of variables 1+2 added.

DinoHunter (still no nVidia compo voucher!), CPU/GPU Benchmark, DarkFish Encryption DLL, War MMOG (WIP), 3D Model Viewer
bitJericho
22
Years of Service
User Offline
Joined: 9th Oct 2002
Location: United States
Posted: 18th May 2008 22:42
I like it, nice and simple is best.

However, it put the second line of my haiku on two lines. Very bad!!


Hurray for teh logd!
bitJericho
22
Years of Service
User Offline
Joined: 9th Oct 2002
Location: United States
Posted: 18th May 2008 22:43 Edited at: 18th May 2008 22:50
PHP, while it may be a hackers dream, is not that bad if you take simple precautions.

Just read up on security There's lots of sites that don't get hacked that use php.

Oh btw, if you want some real hosting I can hook you up (for free), email me if you're interested.

I think this is a great project, I had something similar going at itmustbetrue.org, and will again sometime in the short future


Hurray for teh logd!
GatorHex
19
Years of Service
User Offline
Joined: 5th Apr 2005
Location: Gunchester, UK
Posted: 18th May 2008 22:45
Also make sure to use UTF-8 for your database otherwise it's easy for someone to send some wierd extended characters that will screw up your database if you don't.

DinoHunter (still no nVidia compo voucher!), CPU/GPU Benchmark, DarkFish Encryption DLL, War MMOG (WIP), 3D Model Viewer
NeX the Fairly Fast Ferret
19
Years of Service
User Offline
Joined: 10th Apr 2005
Location: The Fifth Plane of Oblivion
Posted: 18th May 2008 23:43
Quote: "Submissions 1 - 4 of 4
Submission Number: 83"


Eh?


I fail at life. No, really.
Izzy545
20
Years of Service
User Offline
Joined: 18th Feb 2004
Location:
Posted: 18th May 2008 23:48 Edited at: 18th May 2008 23:49
Hi all, thanks for the input...

I've now made a logo for the top and bottom that I think make it look a fair bit better! I've also made it fixed width instead of variable so it doesn't muff up the submissions when the window is too small...

Lots of people are saying the color scheme isn't contrasty enough I guess? I'll try to work something out there...

And thanks for all the security tips, I'll definitely look into those and get them implemented.

Once again, thanks for the feedback!

Edit: NeX> It's using an automatically incrementing number on MySQL for the submission number, that's why it's higher than the actual number of submissions

NeX the Fairly Fast Ferret
19
Years of Service
User Offline
Joined: 10th Apr 2005
Location: The Fifth Plane of Oblivion
Posted: 18th May 2008 23:53
Also, my entry seems to have duplicated. It does so every time I go to the page.


I fail at life. No, really.
bitJericho
22
Years of Service
User Offline
Joined: 9th Oct 2002
Location: United States
Posted: 18th May 2008 23:59 Edited at: 19th May 2008 00:00
Also, you should allow authors to title their poetry.


Hurray for teh logd!
Izzy545
20
Years of Service
User Offline
Joined: 18th Feb 2004
Location:
Posted: 19th May 2008 00:04
NeX> I don't know why that's happening, I assume it's something to do with the way PHP is posting the data to itself... Either way, I've got it to check to see if what's being posted is an exact duplicate of before, and if so it doesn't submit it. That should fix your problem, as well as stopping random copying from people.

bitJericho
22
Years of Service
User Offline
Joined: 9th Oct 2002
Location: United States
Posted: 19th May 2008 00:29
Quote: "NeX> I don't know why that's happening, I assume it's something to do with the way PHP is posting the data to itself... Either way, I've got it to check to see if what's being posted is an exact duplicate of before, and if so it doesn't submit it. That should fix your problem, as well as stopping random copying from people."


That's not really a good way to solve a bug though That's like covering it up under a carpet, unless of course you think he's posting the info again by accident, then there's not a whole lot you can do.

One way to solve it would be to use an intermediate page that redirects back to the homepage (with nothing to post after the auto-redirect page)


Hurray for teh logd!
Izzy545
20
Years of Service
User Offline
Joined: 18th Feb 2004
Location:
Posted: 19th May 2008 00:40
Jerico2Day> haha, true. I didn't even think about doing that, that's a great idea. Thanks

Also, NeX I did make sure to remove all html tags on submissions, thankfully

Comments on the new logos I made anyone?

Zappo
Valued Member
20
Years of Service
User Offline
Joined: 27th Oct 2004
Location: In the post
Posted: 19th May 2008 01:58
Quote: "Also, I find it odd that I have to put my password right on the PHP file I'm using to access my database, any safety concerns there?"

You don't have to put your password in your PHP pages. In fact I would strongly recommend you don't. PHP can include external files which can contain things like passwords or commonly used functions. It can also see folders on your server which are not inside your Web site. What I tend to do is put my database connection strings, password and other sensitive details in an external file which I store in a location not part of the Web site.
Quote: "Learn to program in Perl coz PHP is a hackers dream."

I would strongly disagree with that. With any language, its only a security risk if the person writing it hasn't done a proper job. PHP is as good as any other server side language, and better than most. If your site security is done right then the source code is just as hidden as a Perl program, shell script or anything else.


Chart data provided with kind permission from ELSPA
Nicholas Thompson
20
Years of Service
User Offline
Joined: 6th Sep 2004
Location: Bognor Regis, UK
Posted: 19th May 2008 15:28
I'm so glad Zappo brought the point up about security! That statement about PHP vs Perl in terms of security is rubbish! I suppose you could argue that Perl has been around for a little longer (about a decade longer) and therefore I suppose you could argue that any core issues might have already been addresses whereas they might still exist in PHP... But, imho, there are bigger issues on a server - for example making sure your SSH accounts are properly configured, having your FTP accounts root jailed, making sure all your accounts have passwords which aren't at risk from dictionary attacks and so on...

On the flip side, there is no point securing your server if you have a PHP script which doesn't sanitize its input!

Regarding your site - I personally quite like that design! Lovely choice of colour and good use of CSS for the rounded corners. One hint, you dont need to do this:


I believe its better practice to simply to:


Dot (.) is a class selector and Hash (#) is an ID collector.

It looks like your site is protected against basic injection (such as using an apostrophe). It also looks like your query arguments are sanitized too.

Nice work!

[center]
Izzy545
20
Years of Service
User Offline
Joined: 18th Feb 2004
Location:
Posted: 19th May 2008 19:59 Edited at: 19th May 2008 19:59
Hey everyone, thanks again for your input and for testing stuff out on the site! I'm glad it's working

I added titles and am trying to add in the ability to let people rate your poems... The only issue I can't think of how to solve is to stop people from rating the same poem more than once. I assume I'll have to somehow get their IP address and store it, then check against it every time they try to rate the poem? But then how would I see what poems they have and haven't rated?

Nicholas> Thanks for the feedback! I'm glad you like the design, are there any other SQL injection maneuvers I might need to be worried about and protect against?

Zappo> I've now stored the sensitive data in another php file on another part of the server, but it seems like that's just putting one more step between someone who wants it getting at it. I'm not running my own server, just using a hosting service, so I don't have many options of where to hide it Maybe I misunderstood what you meant?

bitJericho
22
Years of Service
User Offline
Joined: 9th Oct 2002
Location: United States
Posted: 19th May 2008 20:13 Edited at: 19th May 2008 20:16
Yep, check it against the IP.

What I would do is set up a table with IP addresses assigned to a user id.

This will allow you to keep track of what ips submitted poems as well as what an ip has rated.

Basically, have a second table keep track of what user id's have rated what poems.

Oh, and for zappos comments, the idea is to place your sensitive info in a non-http accessible location. Like your public http docs might be under /home/username/http_docs, you would place your sensitive file in /home/username/ and not in /home/username/http_docs.

A proper host would allow you to do this. If you can't, I'd be happy as I said before to set you up with an account on my server


Hurray for teh logd!
Nicholas Thompson
20
Years of Service
User Offline
Joined: 6th Sep 2004
Location: Bognor Regis, UK
Posted: 19th May 2008 23:27
My 2 cents on the stopping duplicate votes would be to log the IP AND the PHP Session ID. Maybe, using MySQL, make the primary key a double-field with the IP and the SessionID as the key.

The reason I suggest this is because there are a LOT of organizations where many machines are represented by one IP - for example where I work. This means that if I voted at work, then nobody else at my work could vote.

If you did session ID's then the plus point would be that votes would be unique to an individual's session. The negative point would be that all it takes is for the user to delete their cookie or empty their cache and, as far as the server is concerned, they are a new user. This is why I suggest logging the IP also. Maybe also log the unix timestamp for the vote too (that way you could use some funky SQL to keep an eye out for spikes in votes.

This is simply the way *I'D* do it... I have a habit of over-complicating/planning for edge cases.

Another option open to you, which might not be AS educational as doing it yourself, is to use a CMS such as Drupal which handles Polls, voting, blogs, comments, etc. It also handles all this 'security' stuff for you or at least gives you a platform to work upon which is inherently a 'bit' secure - for example, in Drupal an SQL insert query would be written...

In that snippet the db_query function is a 'core' function which is part of the database layer which allows Drupal to work on MySQL, PostGr SQL and I believe they have a working Oracle model too. The table name has a {} wrapped around it as this allows the db_query function to rewrite table names to all have custom prefixes. The REALLY funky bit is the placeholders. '%d' represents a decimal number - any variable that gets put in here its forced to be a number first. The '%s' tells Drupal that a string is going in that place - all strings get escaped and sanitized on entry. The other arguments to the function are the variables which get placed in the %d, %s and other placeholders.

As for putting your username/password into a separate PHP document in a non-accessible place... I'm not personally sure how effective this really is. Ever if you had a separate include PHP file, as long as it doesn't echo anything out itself then even if you called it manually, it wouldn't ACTUALLY tell you anything. For example, all drupal installs store the database username and password in a file called 'settings.php' which is stored in your 'sites' folder under yous specific site folder (a single Drupal install can host multiple sites). For example, you might have it in 'http://www.example.com/sites/example.com/settings.php'. This file simply defines a load of configuration options - if you access it directly then you still cant do anything with it.

[center]
bitJericho
22
Years of Service
User Offline
Joined: 9th Oct 2002
Location: United States
Posted: 19th May 2008 23:35
Is multiple voting really that much of a problem anyway? I'm with Nicholas on this one, use the session ID and IP, and call it a day. If someone gives themselves multiple votes than good for them

When the time comes that it actually matters, you can force people to sign up with a confirmed email address.


Hurray for teh logd!
Nicholas Thompson
20
Years of Service
User Offline
Joined: 6th Sep 2004
Location: Bognor Regis, UK
Posted: 19th May 2008 23:55
Jerico - I completely agree... however on a flip side... There are some VERY VERY bored/stupid people out there who think they're really big and clever by writing a bash script that simply renews a session ID on each call and will post to a specific URL. This script can run hundreds of times a minute.

Yeah - the vote of favorite poem isn't going to change the way we think about modern poetry and literature in the 21st century... But it WILL make your site look a bit crap if one of your poems has several hundred times more votes than all the rest - all of them a zero out of five...
BUT...
If you log the IP and timestamp, you can quite easily 'DELETE FROM votes WHERE ip="123.123.123.123"'... Or if they've gone a step further and spammed your site for 10 minutes from a number of IP's - then simply wipe that time-frame (if you also record the timestamp of the vote).

Of course, this means each vote costs you the storage of an IP (4 bytes?), a session ID (32 bytes), a timestamp (4 bytes?) and the vote itself (a byte?)... This means every person who says "yeah, 5 stars) costs you over 40 bytes. This might not sound a lot - but it adds up VERY quickly. 40 bytes is a lot for a "good" rating. So you have to weigh the work, storage and all those implications against 'how important is this voting system?'

[center]
bitJericho
22
Years of Service
User Offline
Joined: 9th Oct 2002
Location: United States
Posted: 20th May 2008 00:01
Surely you don't need to keep the records past a certain time frame. So those 40 byts can easily turn back into 1 byte after enough time has passed to discount bad users.


Hurray for teh logd!
Nicholas Thompson
20
Years of Service
User Offline
Joined: 6th Sep 2004
Location: Bognor Regis, UK
Posted: 20th May 2008 00:13
Valid point.

Thats a pretty advanced system though to aggregate votes after a certain date.

I suppose you'd also lose the ability to analyze on a nice granular level.

This is why people do Project Analysis/Specification first

[center]
Zappo
Valued Member
20
Years of Service
User Offline
Joined: 27th Oct 2004
Location: In the post
Posted: 20th May 2008 00:26
Quote: "As for putting your username/password into a separate PHP document in a non-accessible place... I'm not personally sure how effective this really is."

Really its to move it out of the site completely. This way, even if someone sniffs your FTP password or does a brute force attack and gains FTP access to your site they still can't get your MySQL info. Your database could contain lots of personal information and is much more important to protect than the site itself. In order to get access to the file which contains the MySQL password etc. they would need proper server access, and if they get that your stuffed whatever you do unless you use one way encryption on the important stuff.

That reminds me... always make sure you use a different password on your MySQL database than your FTP account. It sounds obvious but you would be surprised how many people cut corners to make their life easier.


Chart data provided with kind permission from ELSPA
bitJericho
22
Years of Service
User Offline
Joined: 9th Oct 2002
Location: United States
Posted: 20th May 2008 00:31
Quote: "That reminds me... always make sure you use a different password on your MySQL database than your FTP account. It sounds obvious but you would be surprised how many people cut corners to make their life easier."


Also, don't use your root password

I use a different username/pass for each site I run.


Hurray for teh logd!
Nicholas Thompson
20
Years of Service
User Offline
Joined: 6th Sep 2004
Location: Bognor Regis, UK
Posted: 20th May 2008 00:54
Zappo - So if they've brute forced your FTP then whats to stop them brute forcing the 'proper server access' that you used to put the file in the secure location in the first place?

If someone's got FTP access to your site then you have pretty big problems anyway - not sure getting your local DB username and password is going to stop them hehe.

I understand what you're saying but, imho, I dont think that is a HUGE problem - especially if you're worried about your FTP being compromised so easily

[center]
Zappo
Valued Member
20
Years of Service
User Offline
Joined: 27th Oct 2004
Location: In the post
Posted: 20th May 2008 12:13
Quote: "I understand what you're saying but, imho, I dont think that is a HUGE problem - especially if you're worried about your FTP being compromised so easily"

Its down to what you consider most important. Your files can always be uploaded again as they won't change very often and you should always have a local copy anyway. Your database probably changes constantly and can contain peoples personal details, even payment details in some instances. Having that information fall into the wrong hands could be disastrous, as well as the loss of data if you have to restore from a backup.
Your FTP needs to be open to the outside world so you can upload to it (unless you own your own server which is in the same building as you), whereas your MySQL service should usually not be accessible from outside the machine itself i.e only localhost can connect to it. That prevents brute force attacks from the Internet on the MySQL service. It is also much less likely people will gain access to a secure shell connection or hack into a VPN link than it is to break in through FTP. Even if they do manage that, then your important 'password' file should be in a location not part of the site, hidden and preferably only readable by the user your HTTP service runs as. This will slow them down and should certainly stop automated/bot attacks as they will have no idea what file to look for. Even someone doing it by hand would need to examine your PHP source code to find the location of the file, then possibly change permissions to read it, then attach to the MySQL database locally in order to view it.
The longer it takes an attacker to find these details and the harder you make it, the more chance there is of them giving up. Separating your services as much as you can and ensuring everything has a different and complex password is your best defence.


Chart data provided with kind permission from ELSPA
Jeku
Moderator
21
Years of Service
User Offline
Joined: 4th Jul 2003
Location: Vancouver, British Columbia, Canada
Posted: 20th May 2008 22:25
Quote: "whereas your MySQL service should usually not be accessible from outside the machine itself i.e only localhost can connect to it."


Hmmm I've never seen a webhost offer this. Is that even possible?


Nicholas Thompson
20
Years of Service
User Offline
Joined: 6th Sep 2004
Location: Bognor Regis, UK
Posted: 21st May 2008 02:45
Do you mean "is it possible to remotely connect to mysql" - if so, then definately yes!

You'd simply define a user like this:


Then, assuming your IP is 123.123.123.123, you could connect to the remote MySQL server by either using a nice GUI like CocoaMYSQL or SQLYog on your desktop by entering the address/IP of the MySQL server along with username and password for the user.
STUPIDLY handy!

[center]
Zappo
Valued Member
20
Years of Service
User Offline
Joined: 27th Oct 2004
Location: In the post
Posted: 21st May 2008 03:03 Edited at: 21st May 2008 03:06
Quote: "Hmmm I've never seen a webhost offer this. Is that even possible?"

As Nicholas said, it's actually easy to set up and I know of hosts which scarily allow you to turn this feature on.
On a closed network (e.g. an Intranet) it can be handy to have one machine set up purely as a MySQL server dishing out several database services for other frontend Web servers to connect to. All your data is in one place making it easier to back up, and it means your Web servers don't have to be power houses because they won't be running complex SQL queries themselves. Of course, one misconfiguration and your server could be available to the world!


Chart data provided with kind permission from ELSPA
Jeku
Moderator
21
Years of Service
User Offline
Joined: 4th Jul 2003
Location: Vancouver, British Columbia, Canada
Posted: 21st May 2008 06:33 Edited at: 21st May 2008 06:36
What I mean was, is it possible to allow *only* localhost users to connect to a MySQL database. I'm sure it is, but I have yet to see a webhost offer that. From all of my experiences with webhosts (too many to count!) you create a database and it's located on a server with an IP address and anyone who knows the IP, username, and password can connect. Having it restrict access to just the local computer, while handy is not realistic. Think about those databases that have to be accessed by several sites at once--- how would that even work?

Maybe granting access for the entire world is only stupid if you have a stupidly easy-to-guess user and password, but just having it locked down to its own server is also, in my opinion, not realistic unless you're smalltime.


Zappo
Valued Member
20
Years of Service
User Offline
Joined: 27th Oct 2004
Location: In the post
Posted: 21st May 2008 14:09
My experiences are the complete opposite. The majority of servers I set up only allow database access to localhost and this is the default behaviour of MySQL (until you start adding user accounts). Most Web sites aren't big enough to require several frontend Web servers so it all ends up running on the same machine. Hosting companies which provide shared hosting usually do it this way.
I have set up MySQL to allow other servers on the local network access but I have never set one up to even have the port visible to the outside world. Its never been necessary and I would serious recommend against it unless absolutely necessary.


Chart data provided with kind permission from ELSPA
Roxas
19
Years of Service
User Offline
Joined: 11th Nov 2005
Location: http://forum.thegamecreators.com
Posted: 21st May 2008 16:46 Edited at: 21st May 2008 16:57
Put your mysql connection to diffrent php file and chmod it 400.

Now your site reads it correctly also the connection file is hidden. Even someone founds it and tries to download it, all he gots is 0kb file.

Oh yes and if you can keep it off your server.

Your signature has been erased by a mod because it was too big

Login to post a reply

Server time is: 2024-11-20 06:15:29
Your offset time is: 2024-11-20 06:15:29