Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Steve Davis
5,332 PointsAny advice to help prevent SQL injection attacks?
From the New York Times article 5 August 2014:
"A mysterious Russian crime gang has amassed the largest ever cache of stolen website passwords – over a billion – which were swiped, one way or another, from poorly secured user databases, it's claimed."
"There is worry among some in the security community that keeping personal information out of the hands of thieves is increasingly a losing battle."
“Hackers did not just target U.S. companies, they targeted any website they could get, ranging from Fortune 500 companies to very small websites,” said Alex Holden, the founder and chief information security officer of Hold Security. “And most of these sites are still vulnerable.”
"But the discovery by Hold Security dwarfs those incidents, and the size of the latest discovery has prompted security experts to call for improved identity protection on the web."
“Companies that rely on user names and passwords have to develop a sense of urgency about changing this,” said Avivah Litan, a security analyst at the research firm Gartner. “Until they do, criminals will just keep stockpiling people’s credentials.”
SQL injection http://en.wikipedia.org/wiki/SQL_injection
How to Keep Data Out of Hackers’ Hands http://www.nytimes.com/interactive/2014/08/05/technology/what-you-need-to-know-with-russian-hack.html
8 Answers
Alan Johnson
7,625 PointsSo if you're just looking at SQL injection from the SQL side, I'll try and show you a simple example of how it all works. I'll just stick variables in with $, as if it were PHP, but SQL injection isn't unique to any particular language.
So let's say you have a password reset form. I can enter my email address into it I'll get an email letting me know how to reset my password. The wrong, sql injection exposed way to write that query would be:
select * from users where email = '$user_submitted_email'
Let's say I'm a little enterprising and know that your email address is steve@example.com. Because the user input above isn't validated, I could change your email address to another in the users table with the input:
something@teamtreehouse.com'; update users set email = 'attacker@example.com' where email = 'steve@example.com
I've specifically crafted the string to be a valid pair of queries, knowing that it's possible that you weren't validating my input. The resulting query would be:
select * from users where email = 'something@teamtreehouse.com'; update users set email = 'attacker@example.com' where email = 'steve@example.com'
The password reset form may not work as expected with that input, but if you aren't validating what I entered, I just reset your email address to my attack address. Then I can submit the reset form with attacker@example.com and get access to your account. If it's your app, it's probably an admin account, even!
Hopefully that helps point out what SQL Injection attacks look like. In terms of ensuring against SQL injection, you'll typically either protect against them by validating any input you're using in a query or using parameterized queries. How you do either depends on the database and programming language you're using.
Samuel Rueby
22,538 PointsIn absolutely all cases, never trust input that comes from the client, even if it's not going into the database. That even includes HTTP headers, such as User-Agent. All client input is a possible attack vector.
Philip Cox
14,818 PointsAny time you take input from a user, before you do anything with it, run it through PHP's htmlspecialchars if your using PHP. This escapes any possible injection of malicious code into your database.
Also, connecting to a database with PHP, use PDO statements. You can bind your input parameters to you queries rather than enter the input variable directly.
You should be looking to filter any input, and escape any output.
Ryan Boone
26,518 PointsIn PHP, you can use db2_prepare and db2_execute to safely run SQL queries. Read more about it here.
Ryan Boone
26,518 PointsI think the real issue here is the ineffectiveness of passwords as a security measure. It's just bad design. In order for passwords to be effective, you must first come up with something that's very hard to crack, which will inevitably be hard to remember, then you have to continually come up with them for the different things you need access to. Plus, it's recommended that you change them regularly. It's not maintainable.
Yes, there are password solutions like iCloud Keychain and 1Password, but all they really do is create even bigger targets for hackers to attack. The New York Times article only makes this more clear. We need a new solution. BTW, if you know anyone working on one, let me know. I've got some money I'd like to invest.
Alan Johnson
7,625 PointsPasswords are certainly ineffective, but removing passwords would not remove the risk of SQL injection.
Steve Davis
5,332 PointsAlan, I am thinking specifically about MySQL, and any other languages or procedures that may be involved. What are you thinking about? Do you understand the exact mechanism of the database security breach? If so, I would like to hear it. I understand from reading the news accounts that names, passwords, and email addresses were stolen. Perhaps other data was stolen too.
Now, on the other hand, it seems to me that it may tip off hackers to discuss how they are going to be defeated. A double-edged sword, to discuss it or not. I would guess that security experts keep some information private.
Remember the Spy vs Spy comics from Mad Magazine? Perhaps that's before your time. Some great stuff, there.
On a more serious note, I think the subject of database security is obviously a very important one.
From the first two paragraphs of the New York Times article, referenced above:
"A Russian crime ring has amassed the largest known collection of stolen Internet credentials, including 1.2 billion user name and password combinations and more than 500 million email addresses, security researchers say.
The records, discovered by Hold Security, a firm in Milwaukee, include confidential material gathered from 420,000 websites, including household names, and small Internet sites. Hold Security has a history of uncovering significant hacks, including the theft last year of tens of millions of records from Adobe Systems."
The breach last year of Adobe Systems affected me and millions of other people who are subscribers of Adobe Creative Cloud.
The last paragraph of the article:
“The ability to attack is certainly outpacing the ability to defend,” said Lillian Ablon, a security researcher at the RAND Corporation. “We’re constantly playing this cat and mouse game, but ultimately companies just patch and pray.”
Steve Davis
5,332 PointsThanks Alan!
What do you think about Google's new approach to give encrypted sites higher rankings in their search algorithm? Is having an encrypted site going to help prevent SQL injection database breaches? Is there an easy way to do this? I just started looking into it for my Wordpress website.
Here's the news article, published today.
The Independent | Thursday 07 August 2014
Google tweaks search algorithm to give encrypted sites higher rankings
"Google has announced that it will start giving preferable search rankings to websites that offer HTTPS encryption by default.
The world’s most popular search provider has been testing the scheme for months but now plans to roll it out more generally, hoping that it will encourage webmasters to secure their sites.
“We hope to see more websites using HTTPS in the future,” said the company in a blog post announcing the changes."
Alan Johnson
7,625 PointsYou've got a few questions here. I'll address them one at a time:
What do you think about Google's new approach to give encrypted sites higher rankings in their search algorithm?
I think this is wise on their part. We actually intend to get Treehouse switch to being SSL only at some point in the future (we've run into issues with HTML5 video over SSL). Ultimately it's where things are headed in the future.
Is having an encrypted site going to help prevent SQL injection database breaches?
It's not going to help at all. SQL injection has to do with trusting user input, and SSL has no effect on user input. It more ensures that all traffic is encrypted and can't easily be snooped on as the traffic is being transmitted over the internet.
Is there an easy way to do this?
I'm not sure about setting up a Wordpress site specifically, but what you're going to want to look for is information on SSL.
Steve Davis
5,332 PointsAlan, thanks for your answers! Best regards, Steve
Alan Johnson
7,625 PointsAlan Johnson
7,625 PointsWhat specific languages are you thinking about, Steve? That'll help everyone be a bit more specific with our answers.