QL

[edit] Somebody stole my thread title. It should have been 'SQL security'.

Hello.

Can anybody recommend some good literature for SQL security standards?

I don't want anybody but me to be able to connect to my database and start deleting/changing stuff (other than queries, obviously).

(BTW I'm using MySQL 4.1 and HeidiSQL)

Google up "sql injection" This should bring up a whole bunch of security sites.

Basic guidelines.

1 - Create a user with extremely limited permissions. (some places only allow them to run specific packages or procedures)
2 - Use parameterized queries. (sql injection docs describe why very well)

Cover those two and you are already more secure then A LOT of others...

I forgot the most important part. DO NOT TRUST ANY INPUT FROM THE USER! (all caps on purpose)

Test every bit of input for size (right number of characters)

and character set (no quotation marks, special characters, etc...) Unless of course you are expecting a special character.

Use stored procedures and you don't get the problem

> (BTW I'm using MySQL 4.1 and HeidiSQL)

Oops, just read that bit. No stored procedures for you...

First - update to mysql 5.0 if you can.

Second - if not then really, really, really distrust any user input. Limit the user to read permissions only wherever possible. (If possible have a single table the user can modify, and then use the input to that table to do the actual required work elsewhere).

The security issues are more likely to occure because of you scripts rather than the SQL server, but yes, the two tips dynaman gave you are very handy.

these books have helped me as well:
http://www.amazon.co.uk/Performance-MySQL-Advanced-Techniques-Administrators/dp/0596003064/ref=pd_bbs_sr_1/203-2269377-6563965?ie=UTF8&s=books&qid=1176748123&sr=8-1
http://www.amazon.co.uk/Php-architects-Guide-PHP-Security/dp/0973862106/ref=sr_1_1/203-2269377-6563965?ie=UTF8&s=books&qid=1176748137&sr=8-1
http://www.amazon.co.uk/Essential-PHP-Security-Chris-Shiflett/dp/059600656X/ref=pd_bbs_sr_1/203-2269377-6563965?ie=UTF8&s=books&qid=1176748166&sr=8-1

(the first one is more for people with full access to the server)

I have some more books at work, but I can't remember the titles.

If you use PEAR in PHP then be sure to use the prepare method before querying.

Also it might seem a bit daft to wrap a Database wrapper but if you wrap your common commands (Insert, Update, Delete) in a wrapper function then you can more quickly fix any flaws you do encounter.

something like:

/**
* $table_name is the name of the table you want to insert into
* $values is an array of fieldname=>value pairs
* 
* 
* @param string $table_name
* @param array &$values
* @param string $clause = ""
* @author Perturbatio
********************************************************************************/
function insertTableRow($table_name, &$values, $clause=""){
	global $db; //a pear database connection instantiated elsewhere
	$query = 'INSERT INTO '.$table_name.' VALUES ('.createValueString('?', count($values)).') '.$clause;
	
	$args = Array();
	foreach($values as $key=>$value){
		$args[] = $value;
	}
	
	$ins_query = $db->prepare($query);

	$res = $db->execute($ins_query, $args);
	
	if (!PEAR::isError($res)) {
		return mysql_insert_id();
	} else {
		return $res;
		echo $res->getMessage();
		echo $res->getUserInfo();
	}
}

/**
* 
* 
* 
* 
* @param 
* @param 
* @param 
* @author Perturbatio
********************************************************************************/
function createValueString($vals, $count){
	$result = "";
	
	for ($i=0; $i<$count-1; $i++){
		$result.=$vals.", ";
	}
	return $result.$vals;
}

I usually escape the values before insertion into the array, but you could stick that inside the functions as well.

Don't rely on gpc_magic_quotes escaping user data.
Assume that all users are malicious/stupid.
use a wrapper function for escaping values.

i.e.:

/**
* 
*
* @param string $value
*/
function escapeValue($value){
	if (!get_magic_quotes_gpc()){
		return mysql_real_escape_string($value);
	} else {
		$value = stripslashes($value);
		return mysql_real_escape_string($value);
	}
}

/**
* Currently just a wrapper for stripslashes, but could be expanded
*
* @param string $value
******/
function unescapeValue($value){
	return stripslashes($value);
}

Rob - MySQL doesn't support stored procedures until version 5.0.

dynaman - I got as far as creating an 'Anonymous' user with permissions only for SELECT queries. I'm going the right way, here, right?

Yes.

What is the program supposed to be for? If it is a general internet program then all of this security is really a must. If it is an intranet program you can relax a little bit, but even then you never know if it will eventually be put out on the net.

If you are going to have a small number of users then you may want to consider seperate logins for each of them as well.

My site is basically going to be linked to a database table - a list of games; titles, prices, descriptions (hence my last question about string lengths etc). SQL queries will be done via PHP.

I plan to add a search facility and filters (card games, puzzle etc) so users can find what they want more easily.

I don't want users to have to login, which is why I need them to have anonymous access while keeping my database secure. Users will be able to access on a 'read-only' basis. Nobody except me will be able to modify the database table.

An SQL query might look like:

$search = "peggle"; #user input goes here
$query = "SELECT * WHERE Title = " . $search;

i.e. all the user gets to choose is the search string, and has no control over the actual query.

Then you need to be as secure as possible. I'm not a PHP guy but see about locking users out of the login information for the database, that and checking user input should go a long way toward securing things. Especially read up on sql injection and how it works so you can keep it from happening. (But if you disallow any special characters that takes care of most of the problem)

I have no idea how to filter out 'special' characters - I don't even know how to take user input from a text box yet, but it probably isn't overly complex to figure out.

What I'm most likely going to do for the short term at least, is just have some clickable options which will send preset SQL queries to filter out certain types of games (which the user gets absolutely no say in), then add a search facility later when I have more games up there.

I plan on opening my site this Friday (20th April 07), so I'm probably going to open it with just the basics, and upgrade it to have more features later.

Hay GFK, I would do as said above, the problems will not come from the database itself but the scripts your running on your site.

SQL Injections are a big problem, but should be easy to fix.

Make sure that you initilize all of your variables, leaveing one unset on a page that is then used in a query is an easy in for a hacker.

For exmaple.

If you have a page that lists games by their ID, and you list them by passing their ID to a page like this.

SomePage.php?ID=gameidnumberorsomething ( lets say 3)

in the php code you may have something like

$result = mysql_query("SELECT * FROM `Dev_indev` where id=".$ID." LIMIT 0 , 1", $link) or die ('Entry not found : ' . mysql_error());

The problem here is that people can terminate the sql string on their own by parsing something like

SomePage.php?ID=3" AND LIMIT 1,1",$link);

$result = mysql_query("SELECT * FROM `Dev_indev` where id="3" AND LIMIT 1,1",$link); LIMIT 0 , 1", $link) or die ('Entry not found : ' . mysql_error());

The above isn't really function, I don't want to post stuff that can show people how to do this, just trying to give you a heads up.

Another key area are variables that simply are not initialized in your code that also interact with sql queries.

With those people can then do what's called a UNION attack, essentially what they do is inject their own custom SQL UNION query into your database, what it then does is merges all tables, and then they simply pull out things like select password, select user name etc..

one major factor on your side is that your php or asp code is your own and so will be harder for the attacker to find variable names to exploit.

The best thing to do is use obscure variable names and make sure you preset all variables with a value, really its just a matter of not being sloppy. if your sloppy people will find a way to exploit your site.

You should be pretty safe with that. As long you do not create the sql on the fly you should be OK. Users can make a pretend response and send that back.

IE. Doing this is ok. (I don't know PHP, so the syntax is way off)

if option1.checked = true then
sql = "select highscore from scores"

This is not OK
sql = "select " & option1.value & " from highscores"

The user could put possibly put any old garbage into option1.value. Even if it is a checkbox on the screen.

Just as a thing to help stop sql injection is to not name tables and fields obvious things, I know this sounds pretty lame but it stops a lot of casual sql injection. ie, Don't name the password field password and don't name the username field username, likewise add fields like username and password and fill them with spam.

Also as Yavin said, check to see if they're putting any reserved sql command in the input box, likewise on the querystring too.

Ah right, I see. Thx. :)

To stop SQL injection whack all parameters through a function like this :

function quote_smart($value) 
{
	// Stripslashes
  	if (get_magic_quotes_gpc()) 
   		$value = stripslashes($value);

   	// Quote if not a number or a numeric string
   	if (!is_numeric($value)) 
     	  $value = mysql_real_escape_string($value);

   	return $value;
}

and instead of $_GET or $_POST use something like this :

function param($field) 
{
	$temp = $_GET[$field];
	if ($temp == "")
		$temp = $_POST[$field];
		
	if (is_array($temp) == true) 
		return $temp;

	return quote_smart(htmlspecialchars($temp, ENT_QUOTES));
}

PS. You must have a valid mySQL connection for this to work. You CANNOT inject via a form using these methods.

Those are good functions.

Since we're on the topic of MySql and PHP. I've just started working on a function for building HTML forms that are seemingly directly connected to the database.

What I want is to have a php function that goes something like.

$form = new build_form('table','id','column');

Then this function should find the value and datatype of that particluar field (SELECT $column FROM $table WHERE id=$id), build a html form for it with the value. Then use AJAX to update the value directly in the database (i.e. when value changes, send it to post.php that verifies the entered value, inserts it into the database. Then refetches the value and inserts it back into the form, just in case). This will create a complete html form for each field, but for my use it's ok.

I've googled a bit around, and found nothing that does exactly this. There are lots of approaches for submiting a complete form with multiple fields via AJAX, but none that I've found that let you submit each field, radiobox, enum menu etc. as it's changed.. Do any of you know of a library or sample that does this? I would rather save myself some time, and not write it from scratch. :)

Ragnar

I'm no PHP expert, but this sounds like asking for SQL Injection to occur. If this allows direct updating of the database without any data checks I would advise against it.

@Dynaman, it is. the second you put that $column into a select statement a hacker can exploit your code, as long as he knows it of course.

Hiding the table names wont help either the hacker only needs to do whats called a database discovery attack, to sum it up you feed a query with NULL's and read the errors, based on the errors it may say Null is not a string. you know now that the field your at is a string field. you can then work out all tables in the system, and grab any information from them that you want.

SQL and PHP is very powerful, but its goto be respected, make your code as crytic as you can change variable names do sanitize all of your variables before you actually use them, make sure you initialize everything and most of all, double check your code before you use it live.

If your site is going to be serving only a handfull of people your low risk, its your own code so most hackers wont waste their time with it, unless you have something they really want.

If on the other hand your planning to be serving thousands of hits a day on a high traffic site, then I would suggest you put some money into it and pay a security expert(read nice hacker) to test your code.

I should have added that it's for an intranet, which probably will never have more than a 100 users (more likely around 30), so security is not a big issue. I would be doing error checking on the input in the php file that receives the html request, so in theory it wouldn't be any less safe than any other AJAX form submission technique I should think.

Still anyone know of a library that works like this? (see my post above)