Ask Slashdot: Verifying Security of a Hosted Site? 182
edi_guy writes "I'm getting ready to launch a small commercial website that will contain customer information in a MySQL database that will be run by a web-hosting service. While I have good experience with SQL databases from a programming point of view, I'm not an expert on securing them. Given all of the publicity around break-ins and data theft on a seemingly daily basis, it seems prudent to review this now rather than later. What are suggestions on resources that would help verify that both myself and my hosting service are following best practices on securing a database backed website?"
contract some guys (Score:2)
contract some penetration tester like the one from offensivesecurity
Re:contract some guys (Score:5, Interesting)
Not a bad component to have a pen tester come in. You might want to start however by working through a hardening guide like the ones available over at the Center for Internet Security. They are very detailed, easy to follow, and do an excellent job of security your target. Test in development first though as it is too secure in a lot of cases and will kill needed functionality.
Once you've accomplished that have a pen tester look things over and see if its secure. Then put in logging and monitoring, ensure your security controls don't change and that you aren't seeing suspicious activity in the logs.
In terms of evaluating the hosting company, it depends on how open they will be with you. See if they have audit results from PCI or SAS70 and request them. See if they have pen test results available for you as well. Check and make their encryption looks reasonable, are they using SSL etc. Ask their security staff basic questions and see how knowledgeable they are. Request references with highly audited customers to see what they think.
That should keep you busy for a little bit.
Re: (Score:2)
PCI and SAS70 audits are a joke. PCI "tests" are self-administered (and the "scans" are usually run by some firm charging top dollar to run open source vulnerability scanners against an IP or two), and merchant account providers simply ask if you completed and fulfill the requirements. SAS70? A get-rich-quick scheme from financial/accounting firms who perform the audit. Note they aren't technical consulting firms, but accounting firms. *insert troll face/problem? here*
Use SSL. Ensure your site doesn't have
Re: (Score:2)
PCI definitely is a joke but not for the reasons you listed. Self assessments are only done when a company is pretty small and processes a limited number of card transactions. In a large firmer like hosting companies they probably have to have a QSA conduct a formal and more rigorous audit. Companies like heartland were pci compliant when breached so it's definitely not perfect.
But on the other hand a pci and sas70 are most likely the only insight your likely to get into the companies security unless your g
Re: (Score:2)
Do PCI and SAS70 issue certificates that they find your site to be secure? If so that could prove invaluable CYA material in case something does go wrong after all, as you have at least something to prove you tried, and that that you were up to "industry standard".
Re: (Score:2)
Was Sony's PSN infrastructure PCI or SAS70 compliant? Is it going to cost them any less if they are?
Re: (Score:2)
SAS70 has nothing to do with being secure, it's really just a "report on controls placed in operation and tests of operating effectiveness". It's not a checklist audit. What that exactly means varies from company to company (every company's procedures vary) and the firm performing it.
Re: (Score:2)
Ensure your site doesn't have any SQL injection vulnerabilities.
I use parameterized statements where I can, and even in those two cases where parameterized statements are more trouble than they're worth [pineight.com], I do my best to minimize those parts where I have to do manual escaping, and I test those places with "Bobby Tables" style patterns to make sure single quote characters are handled properly. What tools help for finding places that I may have missed?
Encrypt credit card numbers (if you need to store them; preferably you don't).
Ordinarily, I don't. But if I encrypt something in a database, what are best practices to store the decryption keys?
Only allow ports 80/443 from the world
Anythin
Re: (Score:2)
I think that the first step is to watch the design of the website itself and use a layered approach to limit access to services and functionality of the server.
For example - if you use a Tomcat/Java web server it shall be executed in a security manager using a security policy that limits what the application in Tomcat is able to do - like only specific classes may access specific ports/services on the server. And anything in Tomcat shall never access the database itself but instead only a secondary business
Re: (Score:2)
I assumed that he was ready to go live and that he already had the basic covered. But sure first implement what parent post said first. But still I think that before going live the best thing that you can do is to have a penetration test done by a pro. Not some stupid audit shit like PCI (credits cards) and SAS70 (software assurance service).
Re: (Score:3)
Or get it done free: antagonize Anonymous.
Re: (Score:2)
Re: (Score:2)
contract some penetration tester like the one from offensivesecurity
Why pay?
Just buy a domain called iheartsony.com (or similar), load it up with insults against Vladimir Putin and every member of the Chinese politburo and top it off with gay porn images with the head of the king of Thailand 'shopped onto the bodies.
If your server is still standing after a week, it's secure....
(NOTE: This does not guarantee that you will still be standing at the end of the week.)
Re: (Score:2)
a good penetration tester usually get in and tell you in detail how he did it. And if he don't he list you what he tried. If you are not getting that, you are not buying a penetration test conduct by a pro, you are, at best, getting a report from an open source tool that you could run yourself.
Re: (Score:2)
And if he doesn't get in, what does that mean? You have good security or is he a bad tester ?
Re: (Score:2)
It means that you are protected from the list of things that he tried to get in. If he is a pro he is suppose to give you that list.
owasp (Score:4, Informative)
This is a good starting point:
http://code.google.com/p/owasp-development-guide/ [google.com]
Make sure that you setup your mysql server to only accept connections from your server(s).
And get something like Fail2ban if you are using linux to stop brute-forcing.
Store salted hashes of passwords.
Some simple rules that will catch most things (Score:2)
Don't have the SQL server exposed to the internet. Firewall it so only the Web server(s) can access it.
Only expose the web servers HTTP port(s) to the internet and keep them up to date.
Use parameters (and stored procedures) exclusively if at all possible
Re: (Score:1)
These are foregone conclusions, but they do not secure against the most common attacks. It is rare that the SQL server is attacked directly, even if it is listening on the right report -- this is MySQL he's talking about, not MSSQL. Even if you do open mysql port to the world, the footprint is not that large, and common practice is not to create users that can connect remotely.
Use parameters (and stored procedures) exclusively if at all possible
Using parameterized queries exclusively, will avoid most
Re: (Score:1)
Keep things like customer_id and user_id in the session instead. That way no one can change them and submit a POST.
Re: (Score:2)
Stored procedures and other extension-fu are generally a bad idea... not portable when you need to switch SQL implementations.
How many times have you switched SQL implementations on a project? Personally, I've never seen it done. Unless everyone involved is extremely careful to follow only very restrictive ANSI standards, chances are stuff will break. Hell, even LIMIT clauses are generally proprietary. If you think you're going to be switching database backends:
1) Do your research first, and use the right one from the beginning
2) Use an ORM that supports both
Re: (Score:2)
How many times have you switched SQL implementations on a project? Personally, I've never seen it done. Unless everyone involved is extremely careful to follow only very restrictive ANSI standards, chances are stuff will break.
We did it with brilliant success here at work (on a code base from the middle 90) and maybe we are going to move again in the next month. When you are not a lazy slob writing compliant SQL code is not that hard. However you have to have a good dba to make it perform.
Re: (Score:2)
Stored procedures and other extension-fu are generally a bad idea... not portable when you need to switch SQL implementations.
Switching SQL implementation will be easier to manage if you only have to port the SQL procedures instead of having to search all the SQL queries in your application for the DB specific quirks.
Re: (Score:2)
Re: (Score:2)
Ask them (Score:1)
Ask them what their security plan is? How often do they conduct internal and external security scans? Do they conform to any security standards, like NIST, SANS, or even PCI? Also, ensure you have permission to conduct your own security scans, and ask them if you get results where there is a CVSS score of 4.0 or greater.
Re: (Score:2)
Ask them what their security plan is? How often do they conduct internal and external security scans? Do they conform to any security standards, like NIST, SANS, or even PCI?
All eCommerce sites that deal with primary account numbers such as CC numbers (and don't use PayPal for everything) are supposed to be following PCI anyways. It's not as if the PCI requirements are optional rules vendors are allowed to ignore and still process payments --- if you have a payment processor, PCI is going to be incl
Re: (Score:1)
Technically, PCI are voluntary. Quite expensive to find one that will exempt you though. On the level of ~7.6%+$1000/monthly bill when I was last dealing with it.
Some of the most annoying things ever. "We dont process orders through the website, it lists a phone number to call" "Must be PCI Compliant!" "It never even sees customer directed emails, they go through a different machine" "must be compliant!" "Oh, hey, we don't have a website" "Thank you, compliance passed"
Re: (Score:3)
You do realize that PCI compliance covers things like the PoS terminals and the like, right? PCI Compliance is a security guideline document that is supposed to be used if you receive customer credit card information at all.
Period.
Do you use a PoS to process those cards? Is it secured? Is it connected to an open network or on a dedicated line? Is the credit card number printed on the slip? Are those slips secured in a safe place? Does the minimum number of people have access to this slips? etc.
It is
Common Sense (Score:1)
If you don't know how... (Score:2)
...then you have to go back to basic principles, which isn't compatible with a hosted site.
In an ideal world, you'd have a public network (the Internet connection) and a DMZ/semi-private network, with the DB server. The web daemon wouldn't run on the DMZ side, and would have to forward requests through to the other server.
It's been a really long time since I dealt with this stuff firsthand, but I suppose that on a single box one could create a virtual network or use loopback to connect the web daemon to th
PCI Compliance Standards (Score:1)
Follow these: https://www.pcisecuritystandards.org/ and profit.
Penetration Testing (Score:1)
Make them tell you. (Score:5, Informative)
Ask them what their processes and policies are in regards to this. They're your supplier, make them tell you why you should trust them with your DB.
That said....firstly understand that securing the database is a small piece of a very big complicated jigsaw made up of randomly cut pieces with an abstract painting on them. Security is hard.
My first step is always to get the infrastructure up to something I'm happy with.
* Set your firewall to block all incoming connections by default, only ever allow connections to port 80 (and 443 if necessary) on your web server/load balancer.
* Designate a couple of 'management IP addresses'. IE your home, or another location. Open up SSH to these addresses only.
* Configure SSH so the only way to access it is via certificates. Do not allow tunnelled plaintext passwords, ever.
* Try to ensure all your secret SSH keys are password protected
* For all server management issues use SSH. Use it for uploading, direct DB access, deploying etc. The only external connections to any of your servers happen on port 443/80/22.
* If you are using SSL use a secure cipher suite (running SSL Digger) will tell you if you are using any weak ciphers
* Decide on an update policy (ours is to have a human monitor all package updates daily, decide when an important one comes out, test it on stage, then update production) that ensures critical security fixes are applied quickly
* Google "security guide app" and review what the Internet says about securing Apache/Lighttpd/Squid/MySQL/RabbitMQ/Whatever. Understand it! Pay particular attention to anything the user interacts with (ie Joomla/Drupal/Wordpress)
Hmm, that's a pretty big list, mostly incomplete, and isn't even where your big security problems lie - most attack vectors are likely to come through flaws in your application. SQL Injection (shockingly!) is still happening, and if you give users the opportunity, someone WILL shoot themselves in the foot.
Man, security is hard! You can hire an agency to test things for yuo and give you a report. These tend go from quite cheap (ie the firm just ran Nessus and sent you the output) to extremely ellaborate white-box penetration testing that usually comes back with practical real world advice.
Great that you are concerned enough about this to ask Slashdot, don't work for Sony do you ;)
Re: (Score:2)
Yes. Security though obscurity (this what you're suggesting) is bound to failure.
Re: (Score:2)
Is there ever a reason to run SSH on port 22?
Yes, it can make managing other parts of the system (such as the firewall of the provider) simpler. But ensure that you disable logging in with a password with it; it's otherwise just too vulnerable to distributed attacks. Also bear in mind that going from port 22 to port 2222 isn't going to help much anyway; what would be the second port that an attacker would guess? Real security is better than a miniscule amount of obscurity.
Re: (Score:2)
If you're doing all that, why NOT m
Re: (Score:2)
All for security in depth, but personally I think the security benefit of running ssh on a non-standard port (which is making it so most automatic attacks miss you, as you say) is so miniscule that the increase in complexity isn't worth it.
Or put another way - there should be a very compelling reason behind every non-standard change you make, and avoiding automated scans isn't compelling enough for me.
Uhh.... (Score:2)
1. Read up on SQL injection attacks. Escape all parameters coming in on the pipes.
2. Put the db on a different server and firewall it so it cannot be accessed from the internet. Ideally you would have a 3 layer design with the web host passing requests to an application running on a firewalled server not accessible to the internet, and the application would pass requests through another firewall to the db server running on a machine not accessible to the web host.
3. Don't store anything you don't have to.
4.
There is much to do to secure a MySQL db. (Score:2, Interesting)
1. Do not use the MySQL root pass as the application password. Do not use the root user as your application user.
2. Setup an application specific id and only give it SELECT, UPDATE, DELETE for the specific tables of the application or to the application schema only.
3. Passwords for these ids must be 15 chars long
4. DB Passwords stored in application files must have the correct ACLs.
5. Learn how to use the creation of the user id to lock the db for access only from the application server. For example if yo
SQL operator IN without concatenation? (Score:2)
If you must script, NEVER EVER concatenate SQL. If no ORM is availble, use whatever languages parameterized SQL scheme.
I'm looking for an easy-to-use alternative to concatenation in cases like these [pineight.com]. For the example, the right side of SQL operator IN is a 1-column table:
But some DBMS APIs do not support table-valued parameters, only scalar-value parameters. I could parameterize the values in the table on the right side of IN, but only if I have to know in advance how many placeholders to use (for example, username IN (?, ?, ?)), and
audit (Score:1)
Hire someone to check it out. (Score:2)
As mentioned earlier, hire someone to check out your security measures. Many companies do this. Sadly i can't remember any names off the top of my head, but google is probably not completely clueless.
And obviously: Make sure that the contractors are legit and not just in it to rig the system with backdoors
Start here (Score:2)
Not to belabour the obvious, but why not start here:
http://dev.mysql.com/doc/mysql-security-excerpt/5.5/en/security.html
That and never, ever insert user-supplied data into a query without using the vendor approved escape mechanism, even if you've done your own safety checks.
http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_quote
Stick with tried-and-true (Score:2)
Re: (Score:2)
Wrong DBMS (Score:2)
MySQL has appalling code quality, and a critical piece of proprietary technology. Use PostgreSQL: safer, more standards-compliant, more robust, totally free, more scaleable.
Re: (Score:2)
Re: (Score:2)
It's hard b/c it seems that for many companies they perceive (maybe accurately) Postgres as more expensive to operate in a shared environment than MySQL. I believe there's been a fair bit of work to fix this over the last few years in PG, but it's still a problem in the field. Plus the demand from devs for PG is much lower than MySQL (kind of chicken and egg).
I've found support from providers at all levels for PG. My personal web host is www.geekisp.com and they do a great job giving me the tools I want, in
Re: (Score:2)
Can you expand on why would PostgreSQL be more expensive to operate? My experience is ðe opposite.
Re: (Score:2)
This is second hand info but the providers I've talked with over the years say that partitioning users, databases, rights, resources, etc in MySQL is more built from the ground up for a co-sharing environment, whereas PG isn't as easy to setup and manage in that configuration. It can be done for sure (geekisp.com does it well) but maybe it requires more expertise or care/feeding or something. I don't know, but that's what I've been told. HTH
Re: (Score:2)
Sounds like a poor excuse for incompetence Postgres is actually simpler to do properly.
Re: (Score:2)
We are talking about MySQL vs Postgres -- isn't that the general rule between the two for almost everything? The fact is that it's much easier to find shared hosting on the cheap that supports MySQL than PG. I myself never use MySQL -- agreeing with you that Postgres is better for everything I need, at least afaik.
Re: (Score:2)
Demand, demand, demand. It is ðe demand!
So you're going to beat them? (Score:2)
Given all of the public break-ins, of huge companies, don't try.
Re: (Score:2)
No no, use it. Register a domain like SonySomething.com. Put some Sony-related content on the first page and your database (filled with fake data) in the back.
It will take a while for Sony to take your domain from your for cybersquatting. If your content remains safe the whole time, your database is probably secure. If you find the content downloadable from Lulzsec, you have more work to do.
Simple yet effective website security test: (Score:5, Funny)
Re: (Score:2)
And encrypt your passwords [geekosystem.com] with DES... and login as root... and don't forget to smudge taco sauch on that post-it-note with that command "yum update" written on it.
Seriously, don't listen to all the naysayers. Just because you call yourself smart and have a million users doesn't make you smart. And just because you don't have a million users and don't think you're smart doesn't make you stupid. Work hard, subscribe to mailing distribution and software mailing lists, and ALWAYS make it a point to check yo
Not possible on a shared host (Score:3)
If you don't control everything on the box, you can't ensure security.
Regardless of what they claim or what they do, you're essentially sharing the box with hundreds or thousands of other users who potentially have access to run whatever they feel like.
I would suggest a Virtual Private Server on Linode. Your server is yours and security will live or die by how you configure it.
Oblig (Score:2)
http://xkcd.com/327/ [xkcd.com]
Security through Obscurity (Score:2)
Lemme draw some parallels to home burglary. Your opportune thief is going to look at one or 2 attack vectors. If he cannot get in he'll move on. Contrast this with a professional burglar. He'll watch a target for days, weeks or months all while taking precious notes about weaknesses in security like a time of day where the property is unguarded or maybe a fault in your automated garage door. He's determined to get inside for something and eventual
It's not rocket science. (Score:4, Informative)
Easy -
Request, log, and record, only that information that is absolutely necessary and nothing more, and keep it only for as long as you'll need it and not a bit longer.
You can save yourself some heartache by not storing any PII and PFI.
Don't store payment information.
Don't store credentials. Consider using OpenID or Google or (shudder) Facebook Connect for accounts.
Keep sensitive information off any internet-accessible systems.
And last, don't trust any input from your visitors.
Sanitize all input.
Declare all variables.
Don't assume anything.
If you're expecting an integer, make sure you convert the submitted form data to an integer for that variable implicitly.
Same for dates, strings.
Normalize all input.
Sanitize all input.
Never trust any input.
Consider using a database abstraction library with well documented and mature APIs. Don't code things yourself.
Don't turn on ssh password authentication. Require only public/ private keys.
Turn register globals off in PHP. Use safe mode.
Make sure MySQL is on a separate server, with an RFC-1918 address, accessible from a dedicated NIC that is not on the Internet.
Consider a security audit and professional code review if you're planning on taking any money.
Re: (Score:2)
Turn register globals off in PHP. Use safe mode.
Yes on the first, be aware on the second. It's been deprecated, as noted at http://php.net/manual/en/features.safe-mode.php [php.net] Safe mode is really a band-aid on an open wound. mod_security, suhosin, proper file ACLs, etc. are all likely better options for dealing with the sorts of things safe_mode buys you, and all but suhosin are applicable to anything that isn't PHP.
Aside from that, a good list. The one thing that can't be said enough: NEVER TRUST CLIENT INPUT. VERIFY, VERIFY, VERIFY.
Re: (Score:2)
You know that just about none of this information is at all helpful or relevant, right? It's a hosted solution; he has no control over any of this. Furthermore, he's asking about database security from the perspective of the hosting provider; 'sanitize all input' isn't something they can do for him. And finally, if your PHP hardening advice consists of "Turn register globals off in PHP. Use safe mode," I really have to wonder if you are just quoting something you heard in a seminar once during your 3-ye
Bottom up (Score:2)
Look through the OWASP guidelines, make notes, and do a thorough code review. No matter how secure the systems are, the application must be secure too. There is a lot more knowledge out there on securing OSes and Web Servers, leave that to your hosting company. You need to concentrate on your application.
Who are you hosting with? (Score:2)
Looking inside a virtual machine (Score:2)
The real problem with shared hosting (virtual or otherwise) means you have to trust the real host not to let someone else at your raw data. Since the real host can see into all the virtual machines, you have to trust it and secure it as well. If your not doing that, who is?
There is only one way. (Score:2)
Best way, hire a good 3rd party auditor sign an NDA with them. You get another set of eyes on the setup. Plus they will use a number of tools to scan your product and the servers you host it on that you may not have easy access to. For example, IBM's AppScan is designed to scan web applications and test for SQL injections, XSS vulnerabilities, etc.
At some point you may want to look at purchasing a copy of AppScan, however that would all depend on how often your code/environment will be changing. WatchFire w
PCI Compliance (Score:2)
If you're holding customer credit card info, you'll need to meet PCI compliance regs. If you're not, you should look at PCI compliance regs anyway, because they're a good guideline.
Then, when that has scared you enough, wake up look up prepared statements, and then don't use MySQL, especially if you are using PHP. Views don't work properly, subqueries bite, and you can't have joins in a delete statement that makes doing compound deletes virtually impossible without serious pain and suffering. If you were
If you don't own the iron ... (Score:2)
Sanitize you inputs (Score:2)
Little Bobby Tables [xkcd.com]
Server User (Score:2)
One thing to check is the userid that your application code (php, jsp or whatever it is) runs under... Many shared hosting providers run all this stuff under a single shared user, which means that user needs to be able to read things like your database config including password... So if someone compromises another customer, they now have the ability to read all your files...
Focusing on the real question... (Score:2)
So, from what I saw, you're asking about the security of the MySQL backend, which is the place where the hosting provider has the most control and you have the least. All the talk about hardening standards that you should read up on is rubbish; you don't get to harden the systems, they're already as hard as they will ever be (whether hard or not) because of the way the hosting provider has provisioned the system. So what you're really into is a situation where you need to know how to spot a secure archite
The 3 most important things about web app sec (Score:2)
Re: (Score:2, Informative)
Second step, don't use a hosted site
Make sure not to use a server application or OS someone else wrote or maintains either. Definitely don't use anything that requires a third party to provide the updates or patches.
On second thought: "Third step, don't connect the web site to the internet."
Seriously... you have to host things, or it will be very expensive to put your site up by building all the infrastructure yourself, or you cut corners and not have the protections (like design, cooling redunda
Re: (Score:1)
No, there's a little thing called a dedicated (or colocated) server. If you are sharing a server, which most people would call "hosted," then you are vulnerable to whatever problems they introduce.
Re: (Score:2)
You can actually get a decent colo fairly cheaply. I am paying $50/mo for my colo, which is a share of a 100mbit pipe with no bandwidth metering. For a low traffic site like the original summary is discussing, that's actually plenty.... it is certainly enough for my colo system, which is primarily a mail server, but also hosts a small low-traffic website and forum. Throw in $1000 for the hardware if you don't have an old server-capable computer kicking around, and you're golden.
And if your e-commerce site i
Its called colocation... (Score:2)
Leasing a colocated server is a relatively inexpensive intermediate option. Most decent ISPs have the option available. Prices typically range from $50 to 200/month depending on hardware specifications/bandwidth requirements.
The next step up is to lease space in a colocation rack from the ISP. That is your hardware in a locked rack in their server room.
Re: (Score:2)
Indeed, also do yourself a favor and make sure that you have at least one set of servers that's within some sort of sane drive from your home or office. The last thing you're going to want to do is drive 6 hours because the people on site don't have a key. Sure you can allow them to have a key, just make sure that there's a sane system in place to track the keys at all times so that there aren't times when some random person has a key or worse where one key can unlock multiple sets of servers.
Re: (Score:2)
It's generally considered (at least among ISP salesmen) that it's colocation if you're sharing your virtual server with someone else's virtual server on a common piece of hardware.
Virtual servers are good, you get a lot of benefits such as low MTTR and easy scalability. But 1RU servers are relatively inexpensive, too -- you can run a single virtual image on your own individual server if you like, everything is negotiable. A variant use of the term is rack-level colocation; your server, their rack.
Re: (Score:2)
[...] get consultants to set everything up.
You can't rely on consultants for security that way. At least not just for "set up".
Security is a continuous processs. Not a thing you do just once.
Many firms specialize in site hosting (Score:5, Insightful)
Hosting secure, high-reliability, high-availabity web sites isn't a do-it-yourself proposition. Perhaps you should have your site hosted by a top quality vendor who has the staff and expertise to maintain the physical and software security.
The problem is that this type of hosting isn't cheap. The bargain basement hosting firms will not provide the expertise and customer support necessary.
Unless you're really going to scale up quickly, it's unlikely that you can hire (or become) expert in all of the domains necessary to provide top tier hosting. For example, can you accurate manage the A/C needs for your site hosting? Do you have guaranteed service contracts on the A/C units an N+1 back ups? Same with power, backups, hot off-site recovery, physical security, insurance, fuel for your generators, battery contracts?
I'd go with a top-tier hosting firm, and be prepared to pay significantly more than $10/month.
Re: (Score:2)
One place to start is to look at netcraft http://news.netcraft.com/ [netcraft.com] they rate the reliability of hosting frims--companies that have been at the top for many months are probably a good recomendation. Look at the client list, and find out what services they are buying. Ask about client service--at top tier the phones should be answered in one or two rings by a extremely knowledgeable tech who can solve the issue. No call trees or escalation. Ideally you'll get an assigned customer service team that takes
Re: (Score:3)
Re:Do not use mySQL (Score:5, Informative)
MySQL doesn't inherently open you up to SQL injection... Poor programming practices opens you up to SQL injection. Any SQL based database is vulnerable if someone stupid writes the program.
The standard lines about SQL injection:
DO use prepared statements with place holders e.g. "SELECT * FROM table WHERE id = ?"
DO NOT use string concatonation "SELECT * FROM table WHERE id = '" + some_string + "'";
DO sanity check anything passed into your database
DO NOT use user input as an identifier (column, table, or view name) E.G. "SELECT * FROM "+user_input+" WHERE 1=1";
DO make users for your database that have the least amount of permissions required to run your app (Only UPDATE, INSERT, DELETE, SELECT)
Re:Do not use mySQL (Score:5, Informative)
Alternately,
DO create stored procs / functions and grant your scripts execute privs (and not much else). This can be a pain to implement, but it is the most "elegant" solution since you're forced to properly decouple DB functionality from front-end logic. Plus it makes it easier to add different front-ends later if you go multiplatform or something...
-or-
DO run all user-sourced input through "mysql_escape_string" or your DBMS' equivalent. Most DAO implementations already do this.
Prepared statements are a decent half-step, but they aren't easily applicable to variable-length queries such as "advanced search" or anything else with optional parameters. It's far too easy to hit one of these walls and fall back into sloppy coding to get around it, negating what little advantage the prepared statements offered.
Re: (Score:2)
DO run all user-sourced input through "mysql_escape_string" or your DBMS' equivalent. Most DAO implementations already do this.
...and make sure to put user input in quotes (or back quotes) in the final query as even an escaped string can cause problems:
Basic query: "SELECT * FROM tbl WHERE x = ?"
Input: "1 OR 1=1"
Final: "SELECT * FROM tbl WHERE x = 1 OR 1 = 1"
Re: (Score:2)
Prepared statements are a decent half-step, but they aren't easily applicable to variable-length queries such as "advanced search" or anything else with optional parameters.
I've generally managed this by dynamically building the query, preparing it, then dynamically building the data statement. Requires a little extra work, but I'll take it any day to mucking with various escape_string methods.
Code wise, I've found it makes it a little prettier to abstract the "search" from the SQL. So you have a "SearchComponent" object that contains say, field, comparator, value .. your advanced search is comprised of a collection of arbitrary SearchComponents.. you then iterate through this
Re: (Score:2)
Please do not tell people to use mysql_escape_string. That function is broken and does not escape proper.
Also, just because you need to dynamically construct a string doesn't prevent you from using prepared statements. Just need to make sure user input is always passed to the database through parameters.
That being said - don't use MySQL in the first place; handling customer data and especially billing information (OP says customer site) in MySQL is just asking for trouble. ( http://sql-info.de/mysql/gotchas [sql-info.de]
Re: (Score:2)
Prepared statements are a decent half-step, but they aren't easily applicable to variable-length queries such as "advanced search" or anything else with optional parameters. It's far too easy to hit one of these walls and fall back into sloppy coding to get around it, negating what little advantage the prepared statements offered.
I don't like prepared statements for the very same reasons. For this reason, we've built our own prepared-statements-like tool that gives us the same advantages of a prepared statement without requiring us to register the statement first.
A preprocessor takes two inputs: the query with string variables embedded, and an array of inputs to match the string variables. In PHP:
$sql="select lastname from users where login = '@login'";
$vars = array('login' => $_REQUEST['login']);
$exec = PrepareQuery($sql, $vars)
Re: (Score:2)
Prepared statements give you performance too (the DB server caches query optimizations), which your custom solution does not.
Also you are dependent on your PrepareQuery() code to evolve with the SQL features/quirks of the database. And if the DB_escape_string() function is not implemented by the server side of the DB server, you can't rely on it (differences between client driver and server implementation are things that can be exploited for SQL injection). So this is the way towards failure.
Re: (Score:2)
Bobby Tables: A guide to preventing SQL injection [bobby-tables.com]
Poor Programming Practices (Score:2)
> Poor programming practices opens you up to SQL injection.
Insecure programming practices, you mean. They're poor only in 99.9% of situations, i.e. where the need for security justifies the brief additional time. You may not need them, for example, in an in-house diagnostic script you're using for a small company that only two admins can use--provided that you are disciplined enoight not to let that spill over into your more secure coding.
Re: (Score:2)
Bingo. 90% of SQL injection prevented, right there.
If you really need to do this, create a white-listed array of tables it's OK to access in this manner and check the input against that before running the query. Also useful if you want user input to select a class/method to use/execute. That's basically what
Re: (Score:2)
Also, don't listen to clueless Anonymous Cowards on Slashdot who don't know anything but the subject at hand - but comment anyway
Re: (Score:1)
Mod parent up (Score:2)
Only, chroot every service you provide that could be accessed over the network.
Employees = weakest link for most companies (Score:2)
Consider the scenario in which an employee goes rogue: disable firewire port (DMA attacks are easily possible), disable usb ports, lock the server room, immediately lock out/revoke IDs to an employee about to be fired (preferably before they're fired), and for god's sake screen your applicants.
Beyond the many war stories, I went to a security conference back in 1999 (yes, that early) where the then-primary IT security guy for the Navy told us that in real-world penetration testing 'red team' exercises, the average cost to bribe a systems administrator to let you into the machine room and do whatever you want was just $7000.
Re: (Score:2)
Re: (Score:2)