Home » Questions » Best way to prevent SQL injection?

Best way to prevent SQL injection?

Answered
1
0

If user input have inserted into an SQL query directly, the application becomes vulnerable to SQL injection, like in the following example:

$unsafe_variable = $_POST['user_input'];

mysql_query("INSERT INTO table (column) VALUES ('" . $unsafe_variable . "')");

That’s because the user can input something like VALUE'); DROP TABLE table;--, making the query:

INSERT INTO table (column) VALUES('<strong>VALUE'); DROP TABLE table;--</strong>')

What should one do to prevent this?

NOTE: This question was originally posted at StackOverflow.com by Andrew G. Johnson

  • Kelly
    Note that that particular example will not work, because the mysql_ lib does not allow executing 2 queries in one statement. As long as you keep the manipulation in a single statement the injection will work.

    NOTE: This comment was originally posted at StackOverflow.com by Johan

  • demo
    sadfasd sadfsdf sdf
  • You must to post comments
Good Answer
1
1

Use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.

You basically have two options to achieve this:

  1. Using PDO:

    $stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
    
    $stmt->execute(array(':name' => $name));
    
    foreach ($stmt as $row) {
        // do something with $row
    }
    
  2. Using mysqli:

    $stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
    $stmt->bind_param('s', $name);
    
    $stmt->execute();
    
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        // do something with $row
    }
    

PDO

Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In the above example the error mode isn’t strictly necessary, but it is advised to add it. This way the script will not stop with a Fatal Error when something goes wrong. And gives the developer the chance to catch any error(s) which are thrown as PDOExceptions.

What is mandatory however is the setAttribute() line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren’t parsed by PHP before sending it the the MySQL server (giving a possible attacker no chance to inject malicious SQL).

Although you can set the charset in the options of the constructor it’s important to note that ‘older’ versions of PHP (< 5.3.6) silently ignored the charset parameter in the DSN.

Explanation

What happens is that the SQL statement you pass to prepare is parsed and compiled by the database server. By specifying parameters (either a ? or a named parameter like :name in the example above) you tell the database engine where you want to filter on. Then when you call execute the prepared statement is combined with the parameter values you specify.

The important thing here is that the parameter values are combined with the compiled statement, not a SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters you limit the risk of ending up with something you didn’t intend. Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course). In the example above, if the $name variable contains 'Sarah'; DELETE * FROM employees the result would simply be a search for the string “‘Sarah’; DELETE * FROM employees”, and you will not end up with an empty table.

Another benefit with using prepared statements is that if you execute the same statement many times in the same session it will only be parsed and compiled once, giving you some speed gains.

Oh, and since you asked about how to do it for an insert, here’s an example (using PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute(array(':column' => $unsafeValue));

NOTE: This answer was originally posted at StackOverflow.com by Theo

  • Sherry
    While mysqli supports bound parameters, it does not support named parameters — so you'll need to use the WHERE name = ? form.

    NOTE: This comment was originally posted at StackOverflow.com by cbuckley

  • Pamela
    A dumb question: am I 100% safe with parametrized queries or should I "just in case" still sanitize the inputs (e.g. casting numbers to int etc)

    NOTE: This comment was originally posted at StackOverflow.com by nico

  • Theresa
    @nico yes, you'll be safe from SQL injection, but don't forget about other threats such as XSS, CRSF. see stackoverflow.com/questions/2119083/…

    NOTE: This comment was originally posted at StackOverflow.com by therefromhere

  • You must to post comments
8
0

Type cast if possible your parameters. But it’s only working on simple types like int, bool and float.

$unsafe_variable = $_POST['user_id'];

$safe_variable = (int)$unsafe_variable ;

mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

NOTE: This answer was originally posted at StackOverflow.com by devOp

  • Brenda
    This is often used way which is not too good. I am been penetration tester and usually used this to pass arguments like 999999999999999999 which go smoothly through cast and later cause huge error message when passed to 4 bytes integer storage.

    NOTE: This comment was originally posted at StackOverflow.com by Tõnu Samuel

  • Diane
    using intval() should prevent this behavior, right?

    NOTE: This comment was originally posted at StackOverflow.com by devOp

  • Brenda
    Not exactly. Manual says "The maximum value depends on the system. 32 bit systems have a maximum signed integer range of -2147483648 to 2147483647. So for example on such a system, intval('1000000000000') will return 2147483647. The maximum signed integer value for 64 bit systems is 9223372036854775807.". I think it is not clever idea to have PHP app which uses 4 byte ints in MySQL to rely on this "feature". It breaks on 64 bit systems. I think web apps should check for values to remain in some range to be sure.

    NOTE: This comment was originally posted at StackOverflow.com by Tõnu Samuel

  • You must to post comments
6
1

In my opinion, the best way to generally prevent SQL injection in your PHP app (or any web app, for that matter) is to think about your application’s architecture. If the only way to protect against SQL injection is to remember to use a special method or function that does The Right Thing every time you talk to the database, you are doing it wrong. That way, it’s just a matter of time until you forget to correctly format your query at some point in your code.

Adopting the MVC pattern and a framework like CakePHP or CodeIgniter is probably the right way to go: Common tasks like creating secure database queries have been solved and centrally implemented in such frameworks. They help you to organize your web app in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.

NOTE: This answer was originally posted at StackOverflow.com by Johannes Fahrenkrug

  • You must to post comments
1
0

If you want to take advantage of cache engines like redis or memcache maybe DALMP could be a choice, it uses pure mysqli, check this: http://www.dalmp.com/PExecute

Also you can ‘prepare’ your arguments before preparing your query so that you can build dynamic queries and at the end have a full prepared statements query. http://www.dalmp.com/Prepare

NOTE: This answer was originally posted at StackOverflow.com by nbari

  • You must to post comments
1
0

The right way to prevent SQL injection is by using parameterized queries. This means defining the SQL code that is to be executed with placeholders for parameter values, programmatically adding the parameter values, then executing the query. Doing this allows the server to create an execution plan for the query, which prevents any “injected” SQL from being executed. An example will help in explaining this. Let’s use the same script, but I’ll define the SQL query with parameter placeholders:

$sql = "SELECT * FROM Tbl WHERE Username = ? and Password = ?";

Now, I’ll define an array that holds the parameter values:

$params = array($_POST['Username’], $_POST['Password’]);

When I execute the query, I pass the $params array as an argument:

$stmt = sqlsrv_query($conn, $sql, $params);

NOTE: This answer was originally posted at StackOverflow.com by Nirav Ranpara

  • You must to post comments
1
1

As you can see, people suggest you to use prepared statements at the most. Its not wrong, but when your query is executed just once per process, there would be a slightly performance penalty. I was facing this issue but I think i solved it very very sophisticated way – the way hackers use to avoid using quotes. I use it to prevent all possible sql injection attacks.

My approach:

  • if you are expect input to be integer make sure its really integer. In variable-type language like is php is this very important. You can use for example very simple but powerful solution: sprintf("SELECT 1,2,3 FROM table WHERE 4 = %u", $input);
  • if you are except anything else from integer hex it. If you hex it, you will perfectly escape all input. In C/C++ there’s a function called mysql_hex_string(), in php use bin2hex(). Dont worry about that the escaped string will have 2x size of its original length because even if you use mysql_real_escape_string or bin2hex, php have to allocate same capacity ((2*input_length)+1). This hex method is often used when you transfer binary data but I see no reason why not use it to all data to prevent sql injection attacks. Note that you have to prepend 0x or use mysql function UNHEX.

So for example query:

SELECT password FROM users WHERE name = 'root'

Will become:

SELECT password FROM users WHERE name = 0x726f6f74

Perfect escape. No way to inject.

Note that this hex is often used as a sql injection attacks where integers are just like strings and escaped just with mysql_real_escape_string, then you can avoid of use of quotes. For example if you just do something like this:

"SELECT title FROM article WHERE id = " . mysql_real_escape_string($_GET["id"])

attack will very easy inject you. Consider the following injected code returned from your script:

SELECT ... WHERE id = -1 union all select table_name from information_schema.tables

SELECT ... WHERE id = -1 union all select table_name from information_schema.tables where table_name = 0x61727469636c65

But if would the coder of injectable site hex it, no injection would be possible:

SELECT ... WHERE id = 0x2d3120756e696f6e20616c6c2073656c656374207461626c655f6e616d652066726f6d20696e666f726d6174696f6e5f736368656d612e7461626c6573207768657265207461626c65203d2030783631373237343639363336633635

NOTE: This answer was originally posted at StackOverflow.com by Zaffy

  • You must to post comments
1
2

For those unsure of how to use PDO (coming from the mysql_ functions), I made a very, very simple PDO wrapper that is a single file. It exists to show how easy it is to do all the common things applications need done. Works with PostgreSQL, MySQL, and SQLite.

Basically, read it while you read the manual to see how to put the PDO functions to use in real life to make it simple to store and retrieve values in the format you want.

I want a single column

$count = DB::column('SELECT COUNT(*) FROM `user`);

I want an array(key => value) results (i.e. for making a selectbox)

$pairs = DB::pairs('SELECT `id`, `username` FROM `user`);

I want a single row result

$user = DB::row('SELECT * FROM `user` WHERE `id` = ?', array($user_id));

I want an array of results

$banned_users = DB::fetch('SELECT * FROM `user` WHERE `banned` = ?', array(TRUE));

NOTE: This answer was originally posted at StackOverflow.com by Xeoncross

  • You must to post comments
1
0

I use three different ways to prevent my web application from being vulnerable to SQL injection.

  1. Use of mysql_real_escape_string(), which is a pre-defined function in PHP, and this code add backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a. Pass the input values as parameters to minimize the chance of SQL injection.

  2. Use of MySQLi.

  3. The most advanced way is to use PDOs.

I hope this will help you.

NOTE: This answer was originally posted at StackOverflow.com by Soumalya Banerjee

  • You must to post comments
1
0

There are many ways of preventing SQL injections and other SQL hacks. You can easily find it on the Internet (Google Search). Of course PDO is one of the good solution. But I would like to suggest you some good links prevention from SQL Injection.

What is SQL injection and how to prevent

PHP manual for SQL injection

Microsoft explanation of SQL injection and prevention in PHP

and some other like Preventing SQL injection with MySQL and PHP

Now, why you do you need to prevent your query from SQL injection?

I would like to let you know: Why do we try for preventing SQL injection with a short example below:

Query for login authentication match:

$query="select * from users where email='".$_POST['email']."' and password='".$_POST['password']."' ";

Now, if someone (a hacker) puts

$_POST['email']= admin@emali.com' OR '1=1

and password anything….

The query will be parsed in the system only upto:

$query="select * from users where email='admin@emali.com' OR '1=1';

The other part will be discarded. So, what will happen? A non-authorized user (hacker) will be able to login as admin without having his password. Now, he can do anything what admin/email person can do. See, it’s very dangerous if SQL injection is not prevented.

NOTE: This answer was originally posted at StackOverflow.com by Manish Shrivastava

  • You must to post comments
1
0

Why are you all reinventing the wheel? A simple way to develop is to use some stable and reliable framework such as Joomla, Drupal or ZendFramework. The choice will rely on your needs and only you can select the appropriate one. If you just start using PHP, maybe it’s more difficult to choose because you need to have some basic knowledge about design patterns…but if you are experimented developer, you should already know that!

NOTE: This answer was originally posted at StackOverflow.com by Yoong Kim

  • Laurie
    why negative votes? It seems logical no?

    NOTE: This comment was originally posted at StackOverflow.com by Yoong Kim

  • Tom
    • Tom
    • 12 years ago
    • 10
    no, grownups don't use frameworks blindly for everything

    NOTE: This comment was originally posted at StackOverflow.com by Andrew G. Johnson

  • Mark
    Benefit of using framework is, they provide database abstraction. They do prepare and security stuff themselves so It is better to use frameworks if it is possible. They let you concentrate on what you want to insert or delete rather than worrying how to make them safer before query execution. well Honestly frameworks are useful when time is a factor and your boss is pain in the ***. ;-)

    NOTE: This comment was originally posted at StackOverflow.com by user1606631

  • Laurie
    yes, that's what I wanted to explain but it seems that other people didn't understand. Well, the problem with coders (and I am a coder too!) is they often want to create THE good algorithm, THE great idea, THE original idea...but most of the time, the solution exists already! And robust frameworks such as Zend Framework already implented some common solutions. So, I still maintain what I wrote : USE existing frameworks guys, don't waste time and money of your company to reinvent the wheel! Think about productivity!

    NOTE: This comment was originally posted at StackOverflow.com by Yoong Kim

  • You must to post comments
1
0

Using this PHP function mysql_escape_string() you can get a good prevention in a fast way.

For example:

SELECT * FROM users WHERE name = '".mysql_escape_string($name_from_html_form)."'

mysql_escape_string — Escapes a string for use in a mysql_query

For more prevention you can add at the end …

wHERE 1=1   or  LIMIT 1

Finally you get:

SELECT * FROM users WHERE name = '".mysql_escape_string($name_from_html_form)."' LIMIT 1

NOTE: This answer was originally posted at StackOverflow.com by Nicolas Finelli

  • You must to post comments
1
0

Parameterized query AND input validation is the way to go. There is many scenarios under which SQL injection may occur, even though mysql_real_escape_string() has been used.

Those examples are vulnerable to SQL injection :

$offset = isset($_GET['o']) ? $_GET['o'] : 0;
$offset = mysql_real_escape_string($offset);
RunQuery("SELECT userid, username FROM sql_injection_test LIMIT $offset, 10");

or

$order = isset($_GET['o']) ? $_GET['o'] : 'userid';
$order = mysql_real_escape_string($order);
RunQuery("SELECT userid, username FROM sql_injection_test ORDER BY `$order`");

In both case you can’t use ‘ to protect the encapsulation.

source : The Unexpected SQL Injection (When Escaping Is Not Enough)

NOTE: This answer was originally posted at StackOverflow.com by Cedric

  • You must to post comments
1
0

Injection Prevention – mysql_real_escape_string()

PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function mysql_real_escape_string.

What mysql_real_escape_string does is take a string that is going to be used in a MySQL query and return the same string with all SQL Injection attempts safely escaped. Basically, it will replace those troublesome quotes(‘) a user might enter with a MySQL-safe substitute, an escaped quote \’.

/NOTE: you must be connected to the database to use this function!
// connect to MySQL

$name_bad = "' OR 1'"; 

$name_bad = mysql_real_escape_string($name_bad);

$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";


$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; 

$name_evil = mysql_real_escape_string($name_evil);

$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
echo "Escaped Evil Injection: <br />" . $query_evil;

you can find more detail here

http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php

NOTE: This answer was originally posted at StackOverflow.com by rahularyansharma

  • You must to post comments
0
0

The simple alternative to this problem could be solved by granting appropriate permissions in the database itself.
For example: if you are using mysql database. then enter into the database through terminal or the ui provided and just follow this command:

    GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED BY 'password';

This will restrict the user to only get confined with the specified query’s only. Remove the delete permission and so the data would never get deleted from the query fired from the php page.
The second thing to do is to flush the privileges so that the mysql refreshes the permissions and updates.

    FLUSH PRIVILEGES; 

more info on flush is given at url: http://www.itechp2pexchange.com/content/mysql-flush-privileges-statement

To see the current privileges for the user fire the following query.

    select * from mysql.user where User='username';

Learn about GRANT at url: http://kb.mediatemple.net/questions/788/HOWTO%3A+GRANT+privileges+in+MySQL#dv

NOTE: This answer was originally posted at StackOverflow.com by apurv nerlekar

  • You must to post comments
0
0

I think if someone want use PHP and MySQL:

  1. Think about lerning PDO
  2. Think about mysqli
  3. Use native php functions like: strip_tags, mysql_real_escape_string or if variable numeric just (int) $Foo

NOTE: This answer was originally posted at StackOverflow.com by RadikCH

  • You must to post comments
0
0

You’ve got two options – escaping the special characters in your unsafe_variable, or using a parameterized query. Both would protect you from SQL injection. The parameterized query is considered the better practice, but escaping characters in your variable will require fewer changes.

We’ll do the simpler string escaping one first.

//Connect

$unsafe_variable = $_POST["user-input"]
$safe_variable = mysql_real_escape_string($unsafe_variable);

mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

//Disconnect

See also, the details of the mysql_real_escape_string function.

To use the parameterized query, you need to use MySQLi rather than the MySQL functions. To rewrite your example, we would need something like the following.

<?php
    $mysqli = new mysqli("server", "username", "password", "database_name");

    // TODO - Check that connection was successful.

    $unsafe_variable = $_POST["user-input"];

    $stmt = $mysqli->prepare("INSERT INTO table (column) VALUES (?)");

    // TODO check that $stmt creation succeeded

    // "s" means the database expects a string
    $stmt->bind_param("s", $unsafe_variable);

    $stmt->execute();

    $stmt->close();

    $mysqli->close();
?>

The key function you’ll want to read up on there would be mysqli::prepare.

Also, as others have suggested, you may find it useful/easier to step up a layer of abstraction with something like PDO.

Please note that the case you asked about is a fairly simple one, and that more complex cases may require more complex approaches. In particular:

  • If you want to alter the structure of the SQL based on user input, parameterised queries are not going to help, and the escaping required is not covered by mysql_real_escape_string. In this kind of case you would be better off passing the user’s input through a whitelist to ensure only ‘safe’ values are allowed through.
  • If you use integers from user input in a condition and take the mysql_real_escape_string approach, you will suffer from the problem described by Polynomial in the comments below. This case is trickier because integers would not be surrounded by quotes, so you could deal with by validating that the user input contains only digits.
  • There are likely other cases I’m not aware of. You might find http://webappsec.org/projects/articles/091007.txt a useful resource on some of the more subtle problems you can encounter.

NOTE: This answer was originally posted at StackOverflow.com by Matt Sheppard

  • Jack
    Note that mysql_real_escape_string() will return false if you don't already have a currently open connection to the database. I have a function called $db->escape(), which checks that a connection is open, opens it if not, and then returns the call to mysql_real_escape_string().

    NOTE: This comment was originally posted at StackOverflow.com by TRiG

  • Billy
    -1 escaping parameters is a form of blacklisting: any failure results in a vulnerability. As @polynomial stated, this is a bad idea. Ideally, mysql_real_escape_string and its ilk would be removed from PHP (given their track record, I won't hold my breath), so as to prevent that false sense of security. Parameterize or be pnwed.

    NOTE: This comment was originally posted at StackOverflow.com by BryanH

  • Curtis
    Here's a very simple wrapper around mysqli: blog.vjeux.com/2009/php/…

    NOTE: This comment was originally posted at StackOverflow.com by singhspk

  • You must to post comments
0
0

You could do something basic like this:

$safe_variable = mysql_real_escape_string($_POST["user-input"]);
mysql_query("INSERT INTO table (column) VALUES ('" . $safe_variable . "')");

This won’t solve every problem, but it’s a very good stepping stone. I left out obvious items such as checking the variable’s existence, format (numbers, letters, etc.).

NOTE: This answer was originally posted at StackOverflow.com by Tanerax

  • Terri
    I have tried your example and it's work fine for me.Could you clear "this won't solve every problem"

    NOTE: This comment was originally posted at StackOverflow.com by Chinook

  • You must to post comments
-1
0

Every answer here covers only part of the problem.
In fact, there are four different query parts which we can add to it dynamically:

  • a string
  • a number
  • an identifier
  • a syntax keyword.

and prepared statements covers only 2 of them

But sometimes we have to make our query even more dynamic, adding operators or identifiers as well.
So, we will need different protection techniques.

In general, such a protection approach is based on whitelisting.
In this case every dynamic parameter should be hardcoded in your script and chosen from that set.
For example, to do dynamic ordering:

$orders  = array("name","price","qty"); //field names
$key     = array_search($_GET['sort'],$orders)); // see if we have such a name
$orderby = $orders[$key]; //if not, first one will be set automatically. smart enuf :)
$query   = "SELECT * FROM `table` ORDER BY $orderby"; //value is safe

However, there is another way to secure identifiers – escaping. As long as you have an identifier quoted, you can escape backticks inside by doubling them.

As a further step we can borrow a truly brilliant idea of using some placeholder (a proxy to represent the actual value in the query) from the prepared statements and invent a placeholder of another type – an identifier placeholder.

So, to make long story short: it’s a placeholder, not prepared statement can be considered as a silver bullet.

So, a general recommendation may be phrased as
As long as you are adding dynamic parts to the query using placeholders (and these placeholders properly processed of course), you can be sure that your query is safe.

Still there is an issue with SQL syntax keywords (such as AND, DESC and such) but whitelisting seems the only approach in this case.

NOTE: This answer was originally posted at StackOverflow.com by Your Common Sense

  • You must to post comments
Showing 18 results
Your Answer
Guest Author
Post as a guest by filling out the fields below or if you already have an account.
Name*
E-mail*
Website
CAPTCHA*
Enter the characters shown on the image.