Sql Injections - What are they, and how to prevent them?

matto

May 1 2006, 04:00 AM

Hey there trap17 community.

As a re-instated (not officially or anything) PHP programmer (i'm about to get started on the first site I've designed in about 2 years..), but never really being into taking care of security issues in the past, I'd like some help in figuring out what SQL Injections are and how I might prevent them from taking place on my website. I know they have to do with putting some symbols into forms on your page, but.... what can specifically be done to counter them?

And I know that there are some XSS things too that serve as some security issues... what exactly are these, and how might they be prevented?

Thank you =D

leeleelee

May 1 2006, 04:05 AM

Sounds too complicated for me. What is the difference and advantage of using PHP over HTML for sites

Saint_Michael

May 1 2006, 04:09 AM

besides it being off topic to answer your question php makes websites more dynamic and also php can be used to make hard work easy if you know what to do.

but topic on hand best way to find out how to prevent sql injections is go to security websites and see what they have for patches and what not.

matto

May 1 2006, 04:11 AM

do you have any you would be so kind as to recommend?

Saint_Michael

May 1 2006, 04:24 AM

Well their is alot of sites but he main issue is that their are patches for anything and everything related to php/mysql you would have to be very speficic in your searches to find hte right patches to correct the problem.

One website I found.
http://www.sqlsecurity.com/

best thing to do is google sql injections and click the first 20 sites and see what they have and what not.

jlhaslip

May 1 2006, 04:29 AM

The first rule of accepting input from a user is "NEVER Trust User Input".

Use techniques like stripping the html tags and escaping "special characters" out of their input before printing or storing the information, altering br's to newlines, basicly assume there is something dangerous to your site in the input and inoculating it before it has a chance to do any damage.

Also, ensure that the access to your data and databases is never revealed. Disable error reporting so errors don't display on a client's screen, remove the files from web accessible paths, using methods that do not reveal the information on a "view source".

These are some of the most common techniques used for securing the site.

WindAndWater

May 1 2006, 05:27 AM

To build on what jlhaslip said, the standard way to do all that is

CODE

$safeVar = stripslashes(strip_tags($unsafeVar));

It's no perfect, but it does protect you against most injection attacks.

If you're considering having any kind of username/password login the only really secure way to do it is to use SSL. However, if you can't do it that way, you can use a javascript implimentation of sha256 (or failing that sha1) to encode the password, after it's added to a predefined key. However, the encoded password can still be intercepted, and used to gain access to a user's account. Under no circumstances use md5 encryption for anything other than generating hashtables.

Kioku

May 14 2006, 10:12 PM

I seriously doubt there's any 100% safe way to protect yourself from attacks like that. jlhaslip raises some good points. It's impossible to completely protect yourself, in my opinion at least. There's always some vulnerablity.

shadowx

May 16 2006, 08:29 PM

As i understand sql injections it is a way to send a modifed query to the database, ithink the simplest and easiest to protect against is writing a query into the url of the querying page. eg:

the proccessing page is proccess.php the normal query is "SELECT * FROM here WHERE blah=$blah"

some pages would store variables in the url or use the GET method so for example:

proccess.php?blah=something_else

a VERY simple sql injection would be loading the page as:

proccess.php?blah=*

then all database entried would be retreived, a simple way to protect against that is using the above methods to protect against special characters being used and any sensitive variables being sent from a form using the POST method, that way no-one can see them and use them for anything and if they try entering variables in the url they will be ignored.

Also what i have done is because i use POST ive told my php to only load the variables from the POST variables

QUOTE

$var2 = $_POST['var1'];

rather than

$var2 = $var1;

i remember with a previous host i could use the second option of not including the POST or GET arrays as it would select them automatically, i thought that could be a problem if someone submits a bad value in the url and it gets selected over the good variable.

i hope that all made sense, just always remember to keep regular backups of files and databases, keep good logs running to track bad users and report them if you need to and let people know you are watching them and will take action.

Bradley

May 16 2006, 09:00 PM

There used to be an extremely dangerous one with the old versions of phpBB where it would be like

CODE

?u=2

where the ' u ' stood for user, you could replace the number 2 with sql queries and insert and retrieve rows to and from the database. This has been fixed. Just make sure you use change characters into values. EX

CODE

would become

CODE

. Secure your forms, make sure they were submitted from your site, not typed in. Look around on google for php security. If you're worried about forum or cms software, most of the exploits have been found and identified so ya wont need to worry about them. Just check your coding if you're doing it manually

Latest Entries

rsf

Jan 24 2007, 04:56 AM

always use addslashes() to usernames to prevent sql injections even if your code doesn't allow them, because you never know when you'll accidentally type error prone code and not realize it.

Also don't allow users to use their scripts on your site. I don't know why you would do this, but this is something you should definately NOT do.

<?
include($_GET['url']);
?>

if you do something like that anyone can include their site into your url and run scripts that query your database for passwords, steal your scripts, whatever.

QuickSilva

Jan 18 2007, 04:12 PM

Oh thank you. I also didn't really know how to fix these security and now you have lightened the scene. Why do people actualy want to wreck people's websites which they have taken a long time to make?

This is a code which I just thought up of looking up at the other posts. Just put this at the top of a page, or better, if you have like a template system, or include a page on each page, just put it in there:

CODE

$_POST = stripslashes(strip_tags($_POST));

Have a great day!

-Tom

daler

Dec 25 2006, 07:57 AM

The following code it blatantly ~~stolen~~ borrowed from http://www.php.net/manual/en/function.mysq...cape-string.php

CODE

<?php
// Connect
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
   OR die(mysql_error());

// Query
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
           mysql_real_escape_string($user),
           mysql_real_escape_string($password));
?>

On a site a few years back for my boyscout troop, I used the mysql_real_escape_string on everything the user potentially had access to, including every sql-query which included either the username or password. In fact, I might have used it on some things that really didn't need it, but then again I think a few extra CPU cycles in the sake of security on a low traffic site won't hurt much.

keri-j

Oct 8 2006, 08:10 PM

Ahhh, the fun of logging in to stupid fansites using things like "hi' or 1=1--"

For the previous person who didn't know why it was "1=1", 1=positive, yes. it's 1=1 because it's saying that the input is the same as the password/username or other field.

It's a shame you can't write tutorials on this kinda thing here in trap17, you can on totse.com

eirikureiriksson

Jul 28 2006, 03:09 PM

Adding my five cents to what already has been said…

1. Do not build SQL strings directly from user input, “select id from user where username = (input) and password = (input)”. The simplest way would be striping the input of any comment marks, line ending marks and quote/string marks and place the input inside a string quotes, “select id from user where username = ‘(input)’ and password = ‘(input)’”.

2. Validate all input and limit it to the right data type, character set, length and values.

3. Remove all unnecessary permissions from all database users.

4. Use account lock-out for repeated failed log-ins.

5. Use views containing just the necessary fields for each query, do not select directly from the tables.

ADD REPLY / Got an Opinion!	Remove these ADs!	RAPID SEARCH!	Free Web Hosting	[X]
Express your Opinions, Thoughts or Contribute more info. to help others. Ask your Doubts & Queries to get answers, So that "Together We can help others!"	Register FREE for AD-FREE forum, Create your own topics, Ask Questions, track topics, setup subscriptions & notifications and Get a Free Website w/ Email and FTP.		500MB Space No Ads, CPanel, FTP, PHP, MySQL, EMails - 100% FREE