SQL Injection

[chesney-93]

I've been looking at ways to prevent this and the most common I can see is the add slash function, what do you think and what do you use?

[Nox]

do not use addslashes function, sql injection is 'user input treated as sql content' which is solved by escaping

escaping means making sure the value is taken literally and not in a context of its surroundings

addslashes is not related to any context, therefore in the matter of escaping quite useless

for escaping values for mysql strings use mysql_real_escape_string or pdo's prepared statements (which are actually not exactly escaping themselves, but has this side effect)

[pirategaspard]

I've been looking at ways to prevent this and the most common I can see is the add slash function, what do you think and what do you use?

Don't bother trying to escape user input, it is not a foolproof solution. You must use prepared statements if you wish to avoid sql injection. Its very easy to do. Check this link for a quick explanation.
http://www.ultramegatech.com/blog/2009/07/using-mysql-prepared-statements-in-php/

[Nox]

Don't bother trying to escape user input

Only in context of the SQL if using prepared statements! and be careful about that

Generally never trust anything that's received from user (from forms, urls, http headers, anything)... actually, many people say never trust any input, thus not even your files or database - because for example you can escape input by prepared statements in SQL context, but if you print it out then, user can have included some JS script - so always escape/validate

[pirategaspard]

Don't bother trying to escape user input

Only in context of the SQL if using prepared statements! and be careful about that

Generally never trust anything that's received from user (from forms, urls, http headers, anything)... actually, many people say never trust any input, thus not even your files or database - because for example you can escape input by prepared statements in SQL context, but if you print it out then, user can sneak in some JS script - so always escape/validate

This is completely true. A user could embed some nasty js in there. Escaping will just not protect you from SQL injection, but escaping is certainly good to protect against other things.

--edited for clarity

[Chris]

addslashes is fine, but it is recommended to use mysql_real_escape_string instead (prevents SQL injection for some exotic charsets).

Also very important thing, make sure integers are integers (or just make a dirty "a='var'" so everything is treates as a string). You need it to prevent integer injection which does not require slashes. Example "WHERE a=$var" hack: "$var=5 OR 0" if you do "WHERE a='$var'" it won't work; of course properly would be to make "$var=(int)$_GET['var']" instead.

[Nox]

addslashes is fine, but it is recommended to use mysql_real_escape_string instead (prevents SQL injection for some exotic charsets).

How is it fine then if it prevents only something?

Also very important thing, make sure integers are integers (or just make a dirty "a='var'" so everything is treates as a string). You need it to prevent integer injection which does not require slashes. Example "WHERE a=$var" hack: "$var=5 OR 0" if you do "WHERE a='$var'" it won't work; of course properly would be to make "$var=(int)$_GET['var']" instead.

good point

[Chris]

addslashes is fine, but it is recommended to use mysql_real_escape_string instead (prevents SQL injection for some exotic charsets).

How is it fine then if it prevents only something?

If you use latin or UTF-8 database charset you are fine with addslashes. Real_escape is require if you set it to some chinese charset or something So, for 99.999% of devs addslashes is technicly identical to real_escape.

[pirategaspard]

addslashes is fine, but it is recommended to use mysql_real_escape_string instead (prevents SQL injection for some exotic charsets).

None of the escaping techniques will be fool-proof for SQL injection. You're essentially creating a character black list and there will always be ways around that. Prepared statements are the ONLY way that will truly eliminate the possibility of SQL injection.  
As Nox said above, you will want to escape your input for other reasons, but not for protection against SQL injection

[codestryke]

Be aware that prepared statements require an additional trip to the sql server. So for every query you execute it must go to the sql server to validate then return and then you submit the prepared query and get your result. Also with prepared statements that means you are forced to use the mySQLi interface, which for version 5.x of PHP, does not allow persistent connections to the database.

[pirategaspard]

So for every query you execute it must go to the sql server to validate then return and then you submit the prepared query and get your result.

Its my understanding that you should only get a double hit on the first request. Once to parse the SQL, and once to get the data. After that the parsed SQL is cached. Because of this prepared statements can be faster than a straight query.  Obviously there will be no speed benefit if you only use a query once however.

Edit: hadn't researched persistent connections before, but PHP manual says you can do them with mysqli as of 5.3

Unlike the mysql extension, mysqli does not provide a separate function for opening persistent connections. To open a persistent connection you must prepend p: to the hostname when connecting.

http://www.php.net/manual/en/mysqli.persistconns.php

[codestryke]

Obviously there will be no speed benefit if you only use a query once however.

Which is why everyone should test for there own usage. I was simply pointing out the pitfalls of mySQLi extension which is not the panacea many point it out to be. For our games this simply is not the case as you have hundreds of players playing 4 different games and games unlike other site are equal to write operations as they are to read which really plays havoc with the query cache.

Edit: hadn't researched persistent connections before, but PHP manual says you can do them with mysqli as of 5.3

Unlike the mysql extension, mysqli does not provide a separate function for opening persistent connections. To open a persistent connection you must prepend p: to the hostname when connecting.

My mistake last time I looked into it, it did not support persistent connections and it was noted they would be supported when 6.x was released.

[Nox]

Which is why everyone should test for there own usage. I was simply pointing out the pitfalls of mySQLi extension which is not the panacea many point it out to be. For our games this simply is not the case as you have hundreds of players playing 4 different games and games unlike other site are equal to write operations as they are to read which really plays havoc with the query cache.

Wouldn't it be benefitial so split it somehow?

As for 5.x ... today PHP 5.3 is already 2 years old! Anything starting with 5.2 and below is a relic and is unsupported and strongly unrecommended ... it's sad 5.3 have so small coverage still ... now when php5.4 is coming (alpha released... with really cool stuff and huge performance improvements) /flame

Of course you can have old projects, but I assume the OP doesn't work on 4 years old legacy code, but building something new

[chesney-93]

Loads of information here, well I'ved done a few bits of everything to just test it a little bit before I do everything.

Could someone give me an example of what to put to test against the injection

[Chris]

Test yourself? What for? Put your website online, google for "hacking forum" and ask them to hack it. Simple, easy and the most accurate

[andrewjbaker]

Try this... http://www.unixwiz.net/techtips/sql-injection.html

[133794m3r]

I'd suggest that you use Vega http://www.subgraph.com/products.html I've ran it against my own pages, and thus far it's shown nothing.(I've already previously made sure that there was no security flaws or issues before using this product and used it as a test to see if it was worth using.)

It's an automated tool, it requires java, but other than that problem, it's really awesome. It's also really fast. Plus it provides information about how to fix things(if you have an issue), I tried enabling directories(and it warned me of it and included more information about it). I suggest that _everyone_ here uses this tool to test their own code, even if they go over everything by hand and go line by line checking for all issues and attack vectors.

[Mutant]

SQL Injection is only one sort of attack you should be concerned about.

Check out https://www.owasp.org/ for others (including details on how to prevent them).

[StudioFortress]

The best strategy I have to avoid security issues, is to just have a good architecture. I believe one reason we have all these security issues on various websites is because the insecure option is also the easiest. So by building a good library, that tackles the issue for me, it makes the secure option the easy option.

For example for SQL injection, I have my own Database class sitting between me and the DB, which automatically generates my SQL queries for me and escapes all inputs. That way 99% of my code, the common cases, prevent SQL by default. So now only the corner cases, the rare occasions I have to write SQL, are when I have to be careful. The other advantage is that if my security is flawed, then I only have to fix it in one place: the Database class.

I do this with all my libraries so I have multiple layers of security. For example with user input I state what I have to state what I think the input should be when it is retrieved (an integers, alphanumeric, a valid e-mail address, matched against a regular expression, and others). If the data fails to match, then 'null' is returned. For example you couldn't pass in a snippet of SQL as an id because it would fail to look like an integer.