Forums: Back End:

Hi guys,

Do you have any tips for stopping SQL injection attacks? Apparently my code is susceptible because I am big dumb coding moran.

Thanks!

I think the main thing is do not use inputboxes in your SQL strings.

for example (in PHP) this would be vulnerable:

"SELECT * FROM tablename WHERE username='$_POST['username']' AND pass='$_POST['pass']'";

Its vulnerable because someone could then enter in the username box at front end something like this

' OR 1=1 --

which would bypass then need for a username and password. they could do something really malicious like DROP the table.

Instead you could assign the contents of $_POST['username'] to a variable and then use a regular expression to check it does not contain possible malicious characters.
Also with the password its generally a good idea to assign to a variable and hash.

easiest thing to do is escape single quotes properly.

also, stored procedures help rather than dynamically creating sql.

As above posts suggests, the main problem is allowing input from a form (or URL parameters or whatever) to be written directly into your SQL. However as long as you're properly escaping and quoting your input, you should never have a problem. You could manually scan input for certain characters, but really it shouldn't be necessary if you're sensible when building your SQL query.

It helps to use some sort of db abstraction layer (e.g. PDO in PHP) and prepared statements (sometimes known as 'parameterized statements') rather than manually concatenating strings to make up your SQL -- that way string fields will automatically be properly escaped and quoted for you.

Please note that SQL injection (trying to subvert your SQL to corrupt/alter/bypass some database operation) is distinct from Cross-Site Scripting (XSS), where the attacker is trying to write script into your pages that will be executed when a visitor views a page.

Cool thanks boys.

After a quick search on the best way to escape single quotes via your advice, I found this on php.net:

        if(get_magic_quotes_gpc()) {
            $product_name        = stripslashes($_POST['product_name']);
            $product_description = stripslashes($_POST['product_description']);
        } else {
            $product_name        = $_POST['product_name'];
            $product_description = $_POST['product_description'];
        }

        // Make a safe query
        $query = sprintf("INSERT INTO products (`name`, `description`) VALUES ('%s', '%s')",
                    mysql_real_escape_string($product_name),
                    mysql_real_escape_string($product_description));
                    mysql_query($query);

sprintf() just inserts the escaped values into the SQL query string.

Originally posted by: Stickman
sprintf() just inserts the escaped values into the SQL query string.