Safe MySQL queries

[Pete]

After realising how easy it is for a malicious user to inject an sql query
into a paramenter for a query, e.g:

$query = "SELECT name FROM employees WHERE ID = ".$HTTP_GET_VARS['id']

And the user enters for the query string: mypage.php?id=1 UNION DISTINCT....

I'm trying to work out what level of protection is needed. As far as I can
see, for integer values I should just validate that a numeral has been
entered, and for text the addslashes() or mysql_escape_string() functions
are enough. Am I right in saying this?

Thanks,
Peter.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.713 / Virus Database: 469 - Release Date: 30/06/2004

[Salil Das]

Pete <so****************@nodomain.com> wrote:

After realising how easy it is for a malicious user to inject an sql query
into a paramenter for a query, e.g: $query = "SELECT name FROM employees WHERE ID = ".$HTTP_GET_VARS['id'] And the user enters for the query string: mypage.php?id=1 UNION DISTINCT.... I'm trying to work out what level of protection is needed. As far as I can
see, for integer values I should just validate that a numeral has been
entered, and for text the addslashes() or mysql_escape_string() functions
are enough. Am I right in saying this?

for integer values that sounds abt right.. i just explicitly cast into an int
or float..
strings should work.. atleast cases where the user input is in quotes should
be fine

SELECT name FROM employees WHERE lastname='SELECT * FROM employees'
^
user input
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Salil Das | It's ok to kiss a fool,
bugs@cc | It's ok to let a fool kiss you,
| but never ever let a kiss fool you.

[Kreso]

Pete wrote:

After realising how easy it is for a malicious user to inject an sql
query into a paramenter for a query, e.g:

$query = "SELECT name FROM employees WHERE ID = ".$HTTP_GET_VARS['id']

And the user enters for the query string: mypage.php?id=1 UNION
DISTINCT....

I'm trying to work out what level of protection is needed. As far as
I can see, for integer values I should just validate that a numeral
has been entered, and for text the addslashes() or
mysql_escape_string() functions are enough. Am I right in saying this?

well , example above, ...WHERE ID = ". $string

if you put $string in ' ".$string." ' then ID will be whatever is in
string, no Injection is possible in that case.
So , right way would be:

$query = "SELECT name FROM employees WHERE ID = '".$HTTP_GET_VARS['id']."';

also, my suggestion is... All GET and POST variables put trough some
checker. All my PHP scripts looks like..

loop
all input trough check ( is number, is string - depends on expected
type )
..
..
..
isset( $_GET['id']) ? $id = $_GET['id'] : $id = false;
so in rest of scrip i don't use GET or POST
--
kreso
www.gdprom.com/mouse/

[CJ Llewellyn]

"Pete" <so****************@nodomain.com> wrote in message
news:40**********************@news-text.dial.pipex.com...

After realising how easy it is for a malicious user to inject an sql query
into a paramenter for a query, e.g:

$query = "SELECT name FROM employees WHERE ID = ".$HTTP_GET_VARS['id']

And the user enters for the query string: mypage.php?id=1 UNION DISTINCT....
I'm trying to work out what level of protection is needed. As far as I can
see, for integer values I should just validate that a numeral has been
entered, and for text the addslashes() or mysql_escape_string() functions
are enough. Am I right in saying this?

The mysql manual recommends that you encase all arguments in single quotes

$query = sprintf("SELECT name FROM employees WHERE ID = '%s'" ,
$HTTP_GET_VARS['id']);

Add to that you should ensure that the id is a number

$id = (int)$HTTP_GET_VARS['id'];
$query = sprintf("SELECT name FROM employees WHERE ID = '%s'" , $id);

You can also prevent attacks by using addslashes if your php installation
doesn't automatically do this for you.

[Malcolm Dew-Jones]

Pete (so****************@nodomain.com) wrote:
: After realising how easy it is for a malicious user to inject an sql query
: into a paramenter for a query, e.g:

: $query = "SELECT name FROM employees WHERE ID = ".$HTTP_GET_VARS['id']

$the_id = mysql_escape_string($HTTP_GET_VARS['id']);

$query = "SELECT name FROM employees WHERE ID = '$the_id'";


--

(Paying) telecommute programming projects wanted. Simply reply to this.