What's the best way of protecting a GET variable?

[Tehim]

I find for just numbers type_digit() works great combined with addslashes() but what about for a mixture, to prevent SQL injection?

[TySkby]

Can you be more specific about what you mean by "protecting"? Do you mean you want to hide/obscure the variable or its params from the user, or something else, like making your application securely use a $_GET variable?

You can't 'protect' the actual data coming in, but you can clean it for DB input.

You can check the data type / validate the value.

If it passes the validation, you then clean it with mysql_real_escape_string(). No need for anything else. Just be careful with integer placeholders in queries. As they aren't quoted, mysql_real_escape_stringed value can still cause havoc - that's why you MUST check to see if the value is an integer/float etc.

[Tehim]

So what kind of things can pass through the mysql_real_escape_string() and inject SQL into a database?

[Tehim]

Can you be more specific about what you mean by "protecting"? Do you mean you want to hide/obscure the variable or its params from the user, or something else, like making your application securely use a $_GET variable?

Protecting a GET variable of SQL injection.

[Tehim]

This is from straight from the php manual:

<?php
// Connect
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    OR die(mysql_error());

// Query
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
            mysql_real_escape_string($user),
            mysql_real_escape_string($password));
?>

Can any SQL injection past through that?

[TySkby]

Nope, I'm pretty sure that's all you need, as ardav said.

To clarify, if you've got a numeric placeholder in the query, it could be a problem:

SELECT * FROM users WHERE user='$u' AND password='$p'

is fine as there are '' single quotes about the placeholder, however, when you get:

SELECT * FROM users WHERE id=$id

is susceptible as there are no '' about the placeholder. So, either put the quotes in - it'll still work, but I'm not sure about a performance hit. OR ensure your validation routine on $id is foolproof.

[Tehim]

So basically...

// Connect
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
    OR die(mysql_error());
 
// Query
$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'",
            mysql_real_escape_string($user),
            mysql_real_escape_string($password));

Will prevent all SQL injections but may create performance hit?

Or find a quicker validation method with less of a hit.

Correct?

No performace hit with this I shouldn't have thought.

[Tehim]

So would this work preventing all types of injection?

preg_replace('/_/', 'html', preg_replace('/%/', 'html', htmlentities(mysql_real_escape_string($apost))));

(Where I've put html is where the entities for each are, it's just they get converted if I put them in.)

As the mysql_real_escape_string() does not escape % and _ (which could be used for wildcards) and htmlentities doesn't convert them then I could just use preg_replace and do it manually.

Right?

I'm fairly new to PHP so sorry if I'm missing a point here.

$user = mysql_real_escape_string($user),
$password = mysql_real_escape_string($password);
$query = "SELECT * FROM users WHERE user='$user' AND password='$password'";

Do you need sprintf? I though you had to have LIKE in the sql to use wildcards, otherwise % is a literal. DOn't quote me on that - I may be wrong!

[digital-ether]

Regarding:

preg_replace('/_/', 'html', preg_replace('/%/', 'html', htmlentities(mysql_real_escape_string($apost))));

You always want mysql_real_escape_string() to be the last operation of that string before including in an sql query. Otherwise it is possible to inject some sql - though unlikely in this example.

An issue is if the mysql encoding does not match that of PHP. This is important since mysql_real_escape_string() makes the mysql encoding into account. The preg_replace() or htmlentities() could return a string with a byte sequence that represents a ' in mysql but really a part of a multibyte character correctly returned by mysql_real_escape_string().

@ardav - good note on using mysql_real_escape_string() only with strings. I've used intval() for intgers but never thought about floats... So you can use floatval() to escape floats?

@digital-ether
I've used is_int, is_numeric, is_float as opposed to forcing data into various types. perhaps rejecting invalid data is safer than second guessing the user's intentions. Mind you, I've used intval too. Sometimes, it's just less hassle.

I may be missing the point here, but is all this preg_replace and sprintf stuff really necessary? Why can't you just clean your variables and write a normal query?

[Tehim]

The preg_replace was to just try and cover some the sql injection that mysql_real_escape_string and htmlentities doesn't cover.

[Steven_B]

First, why not do POST? POST doesn't show up on the address bar.
Second, one thing you want to look may want to look at, including what was talked about already on this topic, is called "sanitation." A google search on the subject will probably pull up adequate info for you!

[Tehim]

First, why not do POST? POST doesn't show up on the address bar.
Second, one thing you want to look may want to look at, including what was talked about already on this topic, is called "sanitation." A google search on the subject will probably pull up adequate info for you!

If you actually bothered to read all the posts properly you would find that we have gone a little off the original topic.

> The preg_replace was to just try and cover some the sql injection that mysql_real_escape_string and htmlentities doesn't cover.

I'm just a little lost as to what it doesn't cover. AFAIK '%' is fine to use as long as you don't use sprintf. Your choice I suppose.

[Steven_B]

If you actually bothered to read all the posts properly you would find that we have gone a little off the original topic.

Tehim, I did read them and saw that this did go a little off topic, but that still doesn't change the fact that your topic is about a GET variable. My question was about that, not what you guys were talking about. Sorry if you thought so.

[digital-ether]

The preg_replace was to just try and cover some the sql injection that mysql_real_escape_string and htmlentities doesn't cover.

More filters or escaping will NOT improve the escaping of a variable. In fact it does the opposite - makes the filter less secure. (an example of this was an XSS injection with a Google website a couple years ago).

mysql_real_escape_string() is what you use to escape strings you pass to mysql. It is the only escape you need for strings as mentioned already.

htmlentities() is the escape for strings you pass to HTML. That is what you write out to a page, and do not want HTML injection, also known as XSS.

htmlspecialchars() is the escape for strings you pass to XML. This is common for AJAX requests and prevents XML injection.

If you use the wrong filter it is useless. If you combine filters, it does not help. When using a filter or escape function you also need to know the encoding of the string being escaped, otherwise it won't work 100%. For example, you cannot use the same filter for utf-8 string as for ASCII string.

When using htmlentities() you need to specificy the encoding with the third parameter. This must be the encoding of the HTML you send to the browser.

With mysql_real_escape_string() the character encoding is taken from the database table, so it is done for you.

In any case you do not need preg_replace() as you cannot match all characters that can be injected. preg_replace actually replaces on the byte level, not character level. So it does not understand what characters are unless you specify this. It will become very complex and just re-inventing what specific functions like htmlentities() or mysql_real_escape_string() already does.