Does anyone know how to keep people from using SQL injection on my site, like stop them from commenting everything beyond the text box out?

Yep there is.

Okay, i don't quite know how to explain, but these are the basics.

Sql injections involve performing unauthorized sql commands such as 'delete `tablename`'; Well, in an attempt to stop these, what you need to do is 2 main things, there are others i believe although can't remember them.

1) firstly make sure that you haven't got majorly changable sql like "truncate `$table`";
2)use two quotes instead of one for forms which the user can type on, this helps greatly.

I learnt php at school ages ago, so am sure this is right. Sorry if it's not.
Hope this helps.

How does that help? Not that I'm saying you're wrong, but double quotes are just as easily broken out of as single quotes.

qwertyiscool, in order for it to be effective, you need to thouroughly 'sanitize' the user's input. One way of doing so may be to check for any characters which you know should not appear in the string - or, as is sometimes easier, making sure there are only characters that can appear in the string. For example, a person's name is only going to contain letters, with the possibility of one or more spaces or full stops (if the name is abreviated) - so something like this could be used:



There isn't really a single universal solution for weeding out SQL injection - it really depends on the situation, and what sorts of data the user is going to input.

I personally try to structure my SQL statments so that the absolute minimum changes are able to occur. So, like sportytalk said, add detail to your SQL statements so that no one can add information to your statement. I also often use parentheses, which make it really hard to change the original statement. If you want the best documentation about how you can structure your statements, I would suggest exploring the SQL documentation at http://www.mysql.com.

Good Luck! And good coding!

Again, using parentheses will do little to provide any sort of protection.

Take this, for example:



This could easily have values injected into it as follows:



One of the reasons that some scripts are so 'easy' to exploit is because they display MySQL errors. Take, for example, IPB (I don't know about this version, but previous versions are guilty as charged) - when a MySQL error occurs, it displays the exact error returned from MySQL, which gives away database structure information and displays which values are being added where. If the attacker doesn't know what MySQL queries are taking place, and isn't aware of the field names and structure of the database, it makes it more difficult for him to effectively inject any SQL statements or modify any conditionals etc. (certainly not impossible, just more difficult). Extracting information can also be tricky if no returned results are sent as output.

hi qwertyiscool,

you can use regular expressions to match invalid input and filter out what can be special characters in a statement like the * or ? etc..

Actually list out a set of inputs that would result in injecting SQL in ur query. Then write statements to filter out such inputs.

In Java you can have a filter class which would filter the input based on regular expression and then can be safely used. I am not sure of the exact solution in PHP but something would surely be possible along similar lines.

Cheers

The most recommended way is using the PHP inbuilt function.


Escapes special characters in a string for use in a SQL statement, taking into account the current charset of the connection. (PHP 4 >= 4.3.0)



Example :

Filtering out the"bad stuff" is essentially what you want to do, but how can you know what all the bad stuff is? A better way to approach this, imho, is to only permit the "safe stuff" and therefore by default you omit anything unsafe.

Regular expressions are really useful for this...allow only a-zA-Z0-9 as legal input. Any other character gets rejected. Well, ok, in practce you might have to permit other characters like the single quote (for names like O'Hare) and the hyphen, maybe one ot two others depending on the app. I have to admit I often also strip out certain character sequences like "exe", "select", "where", etc. If it messes up a user's input to the point that it's unusable, then they might have to use another channel of communication. I think that's a fair price to ask for security.

What I do is strip out any offending character and then send the input through, *maybe* giving the user a peek at it with a short explanation as to why it's different from the original input. Frankly, if they try to slip a pipe into a query, they're lucky if I don't add their IP to a banned list ;-)

Hmm i find by using ( $_REQUEST ) is much safer then ( $_POST ) also like some people have stated here, using statements in your scripts such as , thus would prevent anyone from using anything besides alpha numeric characters ( letters & numbers ) . Now this is basically just preventing people from attacking you with your form , there's lot's of other thing's they can inject through . All depends on what your running.