467,104 Members | 1,013 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,104 developers. It's quick & easy.

Safe MySQL queries

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
Jul 17 '05 #1
  • viewed: 2260
Share:
4 Replies
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.
Jul 17 '05 #2
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/


Jul 17 '05 #3
"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.
Jul 17 '05 #4
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.
Jul 17 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Philip Stoev | last post: by
11 posts views Thread by DJJ | last post: by
3 posts views Thread by frizzle | last post: by
39 posts views Thread by Mairhtin O'Feannag | last post: by
4 posts views Thread by Federico | last post: by
13 posts views Thread by Ciaran | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.