Sql Injection How to stop people from doing this...

[galexcd 0]

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?

[sportytalk 0]

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.

[Spectre 0]

2)use two quotes instead of one for forms which the user can type on, this helps greatly.

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:

 

// Will match if the string contains any characters that are NOT alphabeticall, a period, or a space.if( preg_match('/[^a-zA-Z\. ]/', $_POST['name']) ) {  // Contains invalid characters.}

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.

[arboc7 0]

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!

[Spectre 0]

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

Take this, for example:

mysql_query('SELECT * FROM members WHERE user_name = ("' . $_POST['user_name'] . '")');// The query would become:mysql_query('SELECT * FROM members WHERE user_name = ("Spectre")');

This could easily have values injected into it as follows:

// In the user_name field, simply place '") OR user_name = ("admin"'.// The query would become:mysql_query('SELECT * FROM members WHERE user_name = ("Spectre") OR user_name = ("admin")');

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.

[cse-icons 0]

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

[OpaQue 15]

The most recommended way is using the PHP inbuilt function.

string mysql_real_escape_string ( string unescaped_string [, resource link_identifier] )

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)

This function will escape special characters in the unescaped_string, taking into account the current charset of the connection so that it is safe to place it in a mysql_query().

Example :

<?php $item = "Zak's and Derick's Laptop"; $escaped_item = mysql_real_escape_string($item); printf ("Escaped string: %s\n", $escaped_item); ?>

[mcfly 0]

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 ;-)

[XRated 0]

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

if (!ctype_alnum($variable)) { die ('Illegal Characters'); }

, 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.

[HmmZ 0]

SQL Injections are hard to prevent, because there are so many things you would have to think about when creating your pages, it is hard to memorize every time, here are some methods i advice you to do..

 

Method 1 [Functions]

A good way to implement any security is functions, make a new page and think about every way of preventing SQL Injections and malicious attacks, code it all into functions and all you have to do with any form you use is call the functions, it prevents you from forgetting a way of prevention, but also makes forms easier to overview for you, since all you do is make a form and call up functions.

 

Method 2 [be the attacker]

Another good way of preventing malicious moves from attackers is to pretend to be the attacker yourself, what would you do?

 

Example[register form]

You see a register form and what would an attacker do first? Analyze. The first thing he would do is view the source and construct a mysql query based on his view.

 

Lets look into the source of a register form

<form action="./register.php" method="post"> <p>Username: <input type="text" name="username" /></p> <p>Email: <input type="text" name="email" /></p> <p><input type="submit" value="Register" /></p> </form>

an attacker could easily decide what kind of SQL Statement this brings, since most forms that communicate with mysql use about the same type to insert into a database.

 

A quick guess of an attacker could show (note that passwords are usually not shown in page sources):

<?php $sql = "INSERT         INTO   users (username,                       password,                       email)         VALUES ('{$_POST['username']}',                 '$password',                 '{$_POST['email']}')"; ?>

Does this look equal to your statement? then you have a flaw if you dont have countermeasures, look at the following:

user', 'pass', ''), ('user2

he uses the password "99999" and e-mail "yourname@hotmail.com"

with no security, that is something you can create as username, now look what it does to your sql query:

INSERTINTO   users (username,              password,              email)VALUES ('user', 'pass', ''), ('user2',        '99999',        'yourname@hotmail.com')

because of the used "username" your sql query will be making 2 accounts instead of 1, user will have no e-mail, so he could get a refuse to his register apply, but user2 is added to the database, so he got an account through wich now enables him to look for more sensitive information as registered member. And that is just one of MANY ways they can manipulate your sql querys

 

possible solutions to this problem

So, what minimizes this attacker to get through? Let me sum the current flaws up:

- the table field names matched the names used in the form

- the register form accepts characters as ()', and many more

- the sql query did not stop when an error with the query occured

 

First, make sure the form names will not match the table field names, using php you can do this through variables:

<form action="./register.php" method="post"> <p>Username: <input type="text" name="username" /></p> <p>Email: <input type="text" name="email" /></p> <p><input type="submit" value="Register" /></p> </form>

<?php$thename = $_POST['username'];$thepass = $_POST['password'];$theemail = $_POST['email'];?>

you can name the variables anything you want, as long as it wont match the table field names nor the form names.

 

Then the query becomes the following:

<?php $sql = "INSERT         INTO   users (username,                       password,                       email)         VALUES ('$thename',                 '$thepass',                 '$theemail')"; ?>

Next, make sure your script either eliminates the use of strange characters or return an error on their use

<?php$thename = strip_slashes($thename);$strip="/^[/\()<>,'";:{}^%#!&-*+$]+$/";//characters that username and/or password can be matched with..if(preg_match(($strip,$thename)||($strip,$thepass)||($strip,$theemail)){  print "You may only use characters a-z and 0-9";}else {}//all strange characters have been checked... continue further

All strange characters are checked and if there was a strange character, your script returns an error, using the 2 methods ive shown you, is to my opinion a great way of preventing those nasty attackers from getting through

 

there are many more ways but discussing all of them would take me a few more hours . Just google and see what comes along, anything you find about sql injections is usually very helpful.

 

Hope this helps..