Discussions of secure PHP coding. Security in software is important, so don't be afraid to ask. And when answering: be anal. Nitpick. No security vulnerability is too small.
Hi there - I'm in my first week of coding in php and it's going great so far. I'm developing a booking system for my company and there are two pages; one displays the courses and dates they are available in the year and the second is a generic booking page that, when you click the Book this course link on the first page which contains the name of the table, which half of the year the user is currently looking at and the record id, takes the querystring and uses it to construct a sql query to pull out the right data from the mysql db. Now it's working fine except a fellow programmer took a look at the booking page and said it was at risk from sql injection attacks.
I've had a look around and found some info on this and that you have to escape the characters so how but after that I am pretty lost.
//get courseID
$courseID = $_GET['courseID'];
//get table for query
$tableName = $_GET['table'];
//get which half of the year
$whichHalf = $_GET['whichHalf'];
//set up query
$query = "SELECT * FROM ". $tableName . " WHERE RecordID = " . $courseID;
//echo $query . "<br>";
//connect to database and retrieve record
//connect to db
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die("Unable to select database");
Obviously I've declared my server details but haven't posted them here. As you can see I GET three variables, the courseID, the tableName and whichHalf which is used to determine which half yearly set of months to get from the db. But as you can see my query is exposed. But all the tutorials and help I have seen on line about solving sql injections talk about queries that go SELECT * FROM sometablename WHERE RecordID = " $blahblah; - so does anyone have any ideas of how to make my page more secure and how to sort my query out so it's safe?
its probebly a really bad idea to use get variables in a query becuase those can be realllly easily tampered with. using post would be best and then use mysql_real_escape_string() around the variables and you should be golden
Hi thanks for the reply - I am only in my 1st week of programming in PHP so a lot of what you said didn't mean much. Sorry! Could you possibly reexplain for my inexperienced brain or provide a code example?
Am I correct in doing my link as <a href="bookcourse.php?table=coursesUnixLinux&courseID=<? echo $link ?>&whichHalf=<? echo $whichHalf ?>Book course</a> ?
//get courseID
$courseID = filterAlphanumeric($_GET['courseID']);
//get table for query
$tableName = filterAlphanumeric($_GET['table']);
//get which half of the year
$whichHalf = filterAlphanumeric($_GET['whichHalf']);
//set up query
$query = "SELECT * FROM ". $tableName . " WHERE RecordID = " . $courseID;
//echo $query . "<br>";
//connect to database and retrieve record
//connect to db
mysql_connect($host,$username,$password);
@mysql_select_db($database) or die("Unable to select database");
that preg_replace by patrick would help counter sq injection.
I was commenting only about the validity of his url syntax.
patrick, i hope you are replacing all chars other than the ranges, [a-z], [A-Z], 0-9. do all preg_replace do a global search or act on multiple occurences cos in javascript we use this /g modifier.
LOL - hey people remember I am a PHP virgin here! Less than 7 days experience!! I've taken patrikG's code and I'm trying it out now to see what happens.
Oki doki - I'll leave you php experts to fight it out, but your code seems to be working fine, well it hasn't interefered with the form. I'll have to get it tested for sql injection. Cheers for help.
BTW I was looking for a good manual to get into php and mysql - I was looking at the visual quickpro 'PHP and MySQL for dynamic websites' by Larry Ullman. Thoughts?
sleazyfrank wrote:
BTW I was looking for a good manual to get into php and mysql - I was looking at the visual quickpro 'PHP and MySQL for dynamic websites' by Larry Ullman. Thoughts?
Unless it covers object oriented programming in good detail, I don't think any beginner's PHP book is worth looking at. I usually recommend Harry Fuecks "PHP Anthology".
sleazyfrank wrote:ps patrikG you're in sussex too??
Yup. And I used to work in Horsham for a couple of months some time ago... We always went to the Malt & Shovel
shiznatix wrote:is_numeric() does not always give correct results becuase if there is any number anywhere in the string it returns true which makes no sence