sql injection

aharrover

Hi all:

I've read quite a bit about sql injection and I'm a bit confused. Some places seem to say that using something like mysql_real_escape_string will prevent any manner of injection attacks. Other places seem to indicate that some sort of regular expression is necessary. I use \" in my queries already and am experimenting with mysql_real_escape_string in addition to my \"$variable\" and it doesn't seem to break anything. What's the best way and what will work best with variables that will contain mixed content like street addresses?

Any help or pointers would be appreciated.

bradgrafelman

Let's say you pull data directly from a $_POST field, meaning you're vunerable to SQL injection. The following code would prevent this:

if(!get_magic_qutoes_gpc()) $data = mysql_real_escape_string($_POST['form_field1'], $connection); else $data = mysql_real_escape(stripslashes($_POST['form_field1']), $connection);

NOTE: You must be connected to MySQL for this to work; I referenced the connection with $connection as it is a good practice to do this in MySQL functions.

aharrover

mysql__real_escape_string? I couldn't find mysql_real_escape.....

bradgrafelman

Woops! Sorry! Yes, you are correct. The function is [man]mysql_real_escape_string[/man]. I've changed the error in my previous post.

Kudose

Lets say, hypothetically, that a connection to the database wasnt yet available (since I just learned that a connection is required, you can laugh at me cause I use it all the time and I didnt know).

Would addslashes() do the same thing?

mrhappiness

atm: yes

but i can't imagine a case where you can't wait with escaping after having established a connection

beside of this: mysql_real_escape_string works without a 2nd parameter (the connection)

planetsim

Yes again making sure that [man]get_magic_quotes_gpc[/man] isnt on.

However the connection link isnt required its optional, I personally dont use it as there normally is only one link to the database. But yes it is there for good programming I just dont do it.

Whilst talking about SQL Injections you may want to stop XSS Attacks also. Make sure you validate the data looking into something like [man]htmlentities[/man]

Kudose

Yeah, I use mysql_real_escape_string without an identifier as well, but the manual states that if a connection isnt present (identified or previous), it will error out.

I assumed that addslashes and mysql_real_escape_string did the same thing, but just wanted to be sure.

sneakyimp

i have a question about sql injection....

is it only a concern for INSERT and UPDATE queries or do we also need to worry about SELECT queries?

for instance, is there any danger in something like this?

$fetch_note_sql = "SELECT * FROM " . NOTES_TABLE . " WHERE id='" . $_GET['id'] . "'";

obviously, a user can get any id they want whether the note belongs to them or not so i will add a user_id check as well. but i'm wondering if the user could do any other damage or view other tables?

bradgrafelman

If you don't make the $_GET data safe, they can escape out of your select command and run any SQL query they want, including "DROP", "DELETE", "INSERT", etc.

Also; the mysql_real_escape_string() also escapes any MySQL special characters (asterisks, percentage signs, etc.) along with quotes. That is why the PHP manual recommends running that command on any userdata before using it in a query.

Generall, if you use addslashes(), you'll stop against SQL injections. Someone may be able to mess around with your script a little, and possibly even maliciously fool SQL, which is why it's best just to use mysql_real_escape_string() before running the query. I can't see why you couldn't simply wait until a connection is available to make the userdata safe.

sneakyimp

thanks for the response.

in my case, the connection will be available before i structure any sql.

so you would recommend escaping EACH AND EVERY SINGLE ITTY BITTY PIECE OF DATA entered by a user, retrieved from $GET, $POST, $_REQUEST, or any other data source before sticking it into a query? Shouldn't matter if it's integer/number or text data or whatever. escape it? I just want to be clear on that.

And, just out of curiousity, how could one take a query that already has SELECT in it and 'escape' out of it as you say to add DROP, INSERT, ALTER, etc? just curious about that. i was under the impression you are only permitted to run a single sql statement in mySQL. not sure at all about other databases.

bradgrafelman

Of course not, you can send 1 query to MySQL server and execute multiple queries (example shown below).

If you want to make sure that you're only getting something as simple as integers, then use something like [man]is_numeric/man, [man]is_int/man, etc. If you don't verify each and every piece, someone can find it and use it to their advantage.

Now, for that example I'd talked about above. Let's say you get pages using "?id=" in the URL. Your query (when outputted) looks like this:

SELECT * FROM MyTable WHERE id='5';

assuming I loaded the page using "?id=5" in the address. Now, what if I changed that id variable and you didn't verify?

SELECT * FROM MyTable WHERE id=''; DROP DATABASE mysql;

Both statements will be executed (separate different statements in SQL using a semicolon). The above query could be achieved using the URL "?id='; DROP DATABASE mysql".

sneakyimp

my server setup apparently excludes multiple queries in a single mysql_query() command. i have a script that connects to my db...i added this code:

$sql = "SELECT * FROM notes; INSERT INTO `notes` (`id`, `userid`, `title`, `note`, `url`, `url_title`) VALUES ('', '3', 'I AM THE SQL INJECTION DANGIT', 'IF THIS NOTE APPEARS, YOU COULD SUFFER FROM DROP OR ALTER TABLE COMMANDS.', '', '');";

echo "sql:" . $sql . "</p>";

mysql_query($sql)
  or die("QUERY FAILED:" . mysql_error());

echo 'script complete';

the results are:

sql:SELECT * FROM myplan_notes; INSERT INTO myplan_notes (id, userid, title, note, url, url_title) VALUES ('', '3', 'I AM THE SQL INJECTION DANGIT', 'IF THIS NOTE APPEARS, YOU COULD SUFFER FROM DROP OR ALTER TABLE COMMANDS.', '', '');

QUERY FAILED:You have an error in your SQL syntax near '; INSERT INTO myplan_notes (id, userid, title, note, url, `url_title' at line 1

Do most mySQL servers allow multiple queries? What configuration on mine (or what verson?) might exclude multiple operations in a single query?

I did manage to create some query string variables (using urlencode for quotes) that let me access data i didn't feel like sharing.

I would prefer, if possible, just to simply use mysql_real_escape_string() on any query values rather than writing customized code in each case to determine if the data is valid for a query.

i understand now that any time you take user input and incorporate it directly into a query that you need to first take a look at what you're getting. mysql_real_escape_string seems really useful because it will turn quotes into backslashed quotes. in my case above, if anyone were to attempt sql injection, escaping their &id= value would be sufficient in that it would return nothing. two possible cases

case 1:
(id quoted)
"SELECT * FROM notes WHERE id='$id'"

in this case, if i remembered to escape $id, then the query would still be valid even if a user were to put in quotes and try some injection because quotes would be backslashed. even so, no records would be located that matched an sql fragment because 'id' is an integer field.

i'm guessing the case would be the same for this:
"SELECT * FROM notes WHERE id=\"$id\""

case 2:
(no quotes)
"SELECT * FROM notes WHERE id=$id"

in this case, i'm guessing that sql injection attempts would break the query if there were any quotes in the sql injection because they would all be backslashed, but what about asterisks? OR statements? someone could easily set id to

1 OR id>0

in which case the query would look like:

SELECT * FROM notes WHERE id=1 OR id>0

will mysql_real_escape_string() stop that kind of injection? i'll try some tests and see.

sneakyimp

ok....so after a little research, i have discovered that mysql_real_escape_string() does NOT deal with <, >, *, ;, % or parentheses.

therefore, if chump.php has a query like this:

 $sql = "SELECT * FROM myplan_notes WHERE id=" . mysql_real_escape_string($_GET['id']);

could easily be sql injected by calling:

chump.php?id=1+OR+id%3E0

this results in a query like this

SELECT * FROM myplan_notes WHERE id=1 OR id>0

escaping the string and putting quotes around the $_GET value like this will give mysql_real_escape_string() more power because all quotes will be backslashed:

 $sql = "SELECT * FROM myplan_notes WHERE id='" . mysql_real_escape_string($_GET['id']) . "'";

laserlight

escaping the string and putting quotes around the $_GET value like this will give mysql_real_escape_string() more power because all quotes will be backslashed:

The problem with that is that in the case of numeric values, e.g. an id, it is technically (or at least pedantically) incorrect to treat it as a string - but mysql is forgiving enough to accept it anyway.

Instead of trying to fit the incoming data to what is required, validate the data.
In this case, you expect a numeric value for $_GET['id'], in particular, an integer in base 10, so use ctype_digit() to check it.

A generic escaping function like mysql_real_escape_string() is useful when dealing with a string that can potentially contain harmful SQL/code injection, and yet has no set format.

bradgrafelman

laserlight has a good point.

If you expect an ID, which is an integer, you might want to look at using [man]intval/man or [man]is_numeric/man instead of the mysql_real_escape_string() we've been talking about. The function's name accurately describes its target: strings.

Unless you're using an integer value combined with intval() for verification, you'll probably want to verify the data with mysql_real_escape(). Their may be other instances where different functions would be better suited to the task of verifying the data, but at the moment I can't think of any.

sneakyimp

what i was hoping (naively i suppose) was that i could just cruise with my brain off through my several dozen pages of php code and mindlessly stick in some escaping code (or perhaps hand it off to an apprentice of some kind).

sadly, sql injection appears to be something you have to actively concentrate on to protect yourself against it.

for that particular item, id, intval or is_numeric() will work just dandy i think.

i have other types of records that use varchar fields as a primary index. (the records came from government data). escaping those will probably make me safer. but still i worry.

thanks for the tips. i still wish, though, that there was some kind of easy magic bullet for this.

bradgrafelman

Unfortunately, in today's world, nothing's perfect. There will probably always be some new bug/loophole someone found.

The only way I can see that would completely prevent you from SQL injection is intensive safeties coded into your script, or no automatic insertions (i.e. the data is saved somewhere, awaiting someone's approval/denial to be inserted; very time consuming).

Sorry!

laserlight

for that particular item, id, intval or is_numeric() will work just dandy i think.

intval() is okay, though I think casting to integer might be somewhat faster.
is_numeric() has a slight problem in that it accepts numbers in scientific notation (a.k.a. standard form) - but then one cannot do SQL injection with numbers in scientific notation, assuming that the database spits out an error in time.

Still, I think that it makes more sense to validate this sort of data - just notify the user that the id is invalid. For that, one would check with [man]ctype_digit/man, or use regex.

Drakla

Is there a specific situation you're trying to solve here, or is it just a general thing?

One very very basic way of limiting injection attack on things like passwords / usernames is simply not to allow any spaces. Can you think of any destructive thing you can do with an sql command with one word?