PHP - SQL Injection Attacks!!

[Jado]

Hi

I've just started looking at security under PHP and mySQL

I'm now aware of potential SQL Injection Attacks, but the 2 articles I've
read where both based on ASP scripts!

is PHP just as prone to this type of attack??

I guess it is...

so how do I write my code to stop this type of injection?

I currently use this

$sql = "SELECT * FROM table1 WHERE user='".$user ."' AND pass=' .$pass ."'";

mysql_query($sql);

$user and $pass values are both comming from the URL

I would expect this code to be vulnerable to SQL Injections.....

so how can I paramatise the variables $user and $pass to ensure they are
classed only as parameters and not executable code??

Thanks

Jado

[matty]

- put addslashes($var) instead of ($var) will help

- check the query string of the request for sql content (e.g. SELECT, DELETE, etc)

- only pass numeric id's between pages - that way, you can do things like

$parm1 = $parm1 + 1 - 1;
(instantly turns any string into 0)

and in general, you can make sure that the number *is* a number

[KAH]

matty <squidg...@yahoo.co.uk> wrote in
news:2d8C9.6979$dz2.853662@stones:

> - check the query string of the request for sql content (e.g. SELECT,
> DELETE, etc)

Not really a good idea, it'll take the string
"To delete a file, select it and hit delete" as an attack...

KAH

[matty]

KAH wrote:

OK, or you could try reading what I said in context. If you are *really*
worried about SQL injection attacks, you can block them by:

- using numerical ids where possible, or some other string/variable
format that you can verify using math or some kind of regexp

for text form data
- use regex on variables to make sure they don't form valid SQL
- escape everything, so that it can't inject into query strings, and
build your queries so that injection attempts will create invalid queries
(and trap the errors rather than display them to the user)

Certainly, it's a good idea to check GET urls for SQL language - anything
that needs to be as long as "To delete a file, select it and hit delete"
probably wants to be in a POST request. I personally like to make sure
that a script *won't* fail if someone tries altering the URL, and also
that it can't fail if someone tries putting SQL into the GET variables
or the POST request.

Similarly, you have to be careful about HTML insertions into text fields,
and watch out for any tags you don't want (e.g. <?php, <%, <SCRIPT).

The tricky bit is allowing people to manage their site content, without
allowing crackers to manage it for them.

Matt

[Jado]

Hi Matty

it looks like you've got a good understanding of basic things that can help
protect against common attacks!

i'm pritty new to php, and html for that matter and i think it would help me
greatly to see the design of a single secure php script

the way i've dicided to give people access to there information is via a
Flash GUI. This GUI will be sent to people on CD (not downloaded from my
site). There will be no input forms on my site, only the php scripts for
precessing the requests from the GUI's. Hopefully this aproch is already
making it harder for potential attackers?

After reading a security artical yestarday i tested my script for sql
injection attack and saw for the first time just how easy it is to do!!

but i must have read something wrong because i thought that...
(id is a string)
$id = addslashes($id)

should stop this...

http://url/script.php?id=0%20or%201=1

which it didn't.....

so as i said... i'd like to post a single script here, and maybe get some
help in making it the most secure script on the internet!! :) (if your
willing to help but would rarther use your own scripts, please do.. :)

I'm sure I can look at converting id's to numerical values, but there will
always be a need for string variables too.

here's an example script which returns data as variables to the flash GUI.

for this example the db is a simple one with 2 tables "company" and
"contacts"
with a relationship equal to: 1 "company" may have many "contacts"

in mySQL I haven't set-up a direct relationship between the 2 tables. is
this possible?? (is it really necessary)

there are only 2 scripts. 1 to populate the current company in flash and 1
to populate the current contact in flash

in the flash GUI there are 4 buttons to allow filtering: onsites, telappts,
immatts, misc

each button has a pre defined url attached to it, all pointing to the same
script but with different variables.

to show all companies with an onsite appointment the url currently looks
like this:
http://url/company.php?filter=onsites

has soon as a company record is loaded into the flash GUI a request to the
second script is made to show all relative contacts for the current company
record
to show all contacts relative to the current company record using the
telephone number as the id, the url currently looks like this:
http://url/contacts.php?id=0389127992

The Script: company.php
//*****************************************
<?php
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); #Past Date
header("Last-Modified: " . gmdate("D, d M Y H:I:s") . " GMT");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");
?>
<?php
$link = mysql_connect("host", "user", "pass")
or die("Error Occurred");

mysql_selectdb("test");

if ($filter == "onsites") {
$sql = "SELECT * FROM company
WHERE On_Site_Appointment IS NOT NULL";
}
elseif ($filter == "telappts") {
$sql = "SELECT * FROM company
WHERE Tel_Appointment IS NOT NULL";
}
elseif ($filter == "immatts") {
$sql = "SELECT * FROM company
WHERE Imm_Att = -1";
}
elseif ($filter == "misc") {
$sql = "SELECT * FROM company
WHERE Last_Updated IS NOT NULL
AND On_Site_Appointment IS NULL
AND Tel_Appointment IS NULL
AND Imm_Att = 0";
}
//set-up a counter
$x = 1;

$result = safe_query($sql);
if ($result){
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){

$telno =$row["Tel_No"];
$company = $row["Company"];
$adr1 = $row["ADR_1"];
$adr2 = $row["ADR_2"];
$adr3 = $row["ADR_3"];
$adr4 = $row["Town_City"];
$adr5 = $row["County"];
$adr6 = $row["Post_Code"];
$compcount = mysql_num_rows($result);

echo ("&telno".$x ."=" .$telno ."&company".$x."=".$company
."&adr1".$x."=".$adr1 ."&adr2".$x."=".$adr2
."&adr3".$x."=".$adr3 ."&adr4".$x."=".$adr4 ."&adr5".$x."=".$adr5
."&adr6".$x."=".$adr6);
$x =$x+1;
} echo("&compcount=".$compcount);
}

// The safe_query function definition:

function safe_query ($query = "")
{
global $query_debug;

if (empty($query)) { return FALSE; }
if (!empty($query_debug)) { print "<pre>$query</pre>\n"; }
$result = mysql_query($query)
or die("ack! query failed: "
."<li>errorno=".mysql_errno()
."<li>error=".mysql_error()
."<li>query=".$query
);
return $result;
}

?>
//****************************************

//---------------------------
Script 2: contacts.php
//---------------------------
//****************************************
<?php
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); #Past Date
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");
?>
<?php
$link = mysql_connect("host", "user", "pass")
or die("Error Occurred");
mysql_selectdb("test");
// Create your SQL statement:

$sql = "SELECT * FROM Contact_Info WHERE Tel_No='$id'";

$result = safe_query($sql);
//create counter
$x = 1;
if ($result){
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){

$telno = $row["Tel_No"];
$title = $row["Title"];
$initials = $row["Initials"];
$firstname = $row["First_Name"];
$surname = $row["Surname"];
$position = $row["Position"];
$email = $row["e_mail"];
$contcount = mysql_num_rows($result);

echo
("&conlastupdated".$x."=".$conlastupdated."&contactid".$x."=".$contactid."&t
elno".$x."=".$telno
."&title".$x."=".$title."&initials".$x."=".$initials."&firstname".$x."=".$fi
rstname."&surname".$x."=".$surname
."&position".$x."=".$position."&email".$x."=".$email);

$x =$x+1;
} echo("&contcount=".$contcount);
}

// The safe_query function definition:

function safe_query ($query = "")
{
global $query_debug;

if (empty($query)) { return FALSE; }
if (!empty($query_debug)) { print "<pre>$query</pre>\n"; }
$result = mysql_query($query)
or die("ack! query failed: "
."<li>errorno=".mysql_errno()
."<li>error=".mysql_error()
."<li>query=".$query
);
return $result;
}
?>
//*************************************************************