up vote 5105 down vote accepted

+50

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:

Using PDO (for any supported database driver):

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute(array('name' => $name));

foreach ($stmt as $row) {
    // do something with $row
}

Using MySQLi (for MySQL):

$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
}

If you're connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (e.g.pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

Correctly setting up the connection

Note that when using PDO to access a MySQL database real prepared statements arenot 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 aFatal Error when something goes wrong. And it gives the developer the chance tocatch any error(s) which are thrown as PDOExceptions.

What is mandatory however is the first setAttribute() line, which tells PDO to disable emulated prepared statements and usereal prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to 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 callexecute, 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 an 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));

Can Prepared Statements Be Used For Dynamic Queries?

While you can still use prepared statements for the query parameters, the structure of the dynamic query itself cannot be parametrized and certain query features cannot be parametrized.

For these specific scenarios, the best thing to do is use a whitelist filter that restricts the possible values.

// Value whitelist
  // $dir can only be 'DESC' or 'ASC'
$dir = !empty($direction) ? 'DESC' : 'ASC';

editedSep 26 '15 at 5:54

Your Common Sense

100k 17 86 155

answered Sep 13 '08 at 12:30

Theo

87.8k 8 98 132

Just to add because I didn't see it anywhere else here, another line of defense is aweb application firewall (WAF) that can have rules be set to look for sql injection attacks: – jkerak Dec 29 '15 at 20:11

I woudl like to suggest use redbeanphp orm which is built on pdo. if you use pdo directly,it is really painful. – jewelhuq Jan 2 at 14:24

To learn more about 'PHP Data Objects' i.e. PDO visite these very good articals : 1.code.tutsplus.com/tutorials/… 2.code.tutsplus.com/tutorials/… – rakesh Jan 5 at 11:23

1

Also, the official documentation of mysql_query only allows to execute one query, so any other query besides ; is ignored. Even if this is already deprecated there are a lot of systems under PHP 5.5.0 and that may use this function. php.net/manual/en/function.mysql-query.php – Randall Valenciano Jan 19 at 17:40

2

I disagree with the practice of constantly wrapping every PDO statement in a try/catch. Fatal errors can be good. What's the point of just echo'ing out the errormessage and resuming an erroneous script, when not catching the exception gives you a nice stack trace? – Hugo Zink Feb 11 at 8:03

| show 10 more comments

up vote 958 down vote

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.

Warning:

As of PHP 5.5.0 mysql_real_escape_string and the mysql extension aredeprecated. Please use mysqli extension and mysqli::escape_string function instead

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

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 bymysql_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 byPolynomial 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.

editedMar 31 '14 at 21:44

Denys Vitali

356 3 16

answered Sep 13 '08 at 9:48

Matt Sheppard

46.3k 37 92 110

add a comment |

up vote 487 down vote

I'd recommend using PDO (PHP Data Objects) to run parameterized SQL queries.

Not only does this protect against SQL injection, it also speeds up queries.

And by using PDO rather than mysql_, mysqli_, and pgsql_ functions, you make your app a little more abstracted from the database, in the rare occurrence that you have to switch database providers.

editedMar 7 '14 at 6:12

Amarnath Balasubramanian

3,822 5 16 40

answered Sep 13 '08 at 0:02

Kibbee

43.1k 24 116 159

add a comment |

up vote 471 down vote

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 white-listing seems the only approach in this case.

Update

Although there is a general agreement on the best practices regarding SQL injection protection, there arestill many bad practices as well. And some of them too deeply rooted in the minds of PHP users. For instance, on this very page there are (although invisible to most visitors)more than 80 deleted answers - all removed by the community due to bad quality or promoting bad and outdated practices. Worse yet, some of bad answers aren't deleted but rather prospering.

For example, there are still(1) many(2) answers(3)suggesting(4)you(5) manual string escaping - an outdated approach that is proven to be insecure.

Or there is a slightly better answer that suggests just another method of string formatting and even boasts it as ultimate panacea. While of course it is not. This method is no better than regular string formatting yet it keeps all its drawbacks: it is applicable to strings only and, as any other manual formatting, it's essentially optional, not obligatory measure, prone to human error of any sort.

I think that all this because of one very old superstition, supported by such authorities likeOWASP or PHP manual, which proclaims equality between whatever "escaping" and protection from SQL injections.

Despite of what PHP manual said for ages, *_escape_string by no means makes data safe, and never has been intended to. Beside of being useless for any SQL part other than string, manual escaping is wrong because it is manual as opposite to automated.

And OWASP makes it even worse, stressing on escaping user input which is an utter nonsense: there should be no such words in the context of injection protection. Every variable is potentially dangerous - no matter of the source! Or, in other words - every variable have to be properly formatted to be put into query - no matter of the source again. It's destination that matters. The moment a developer starts to separate the sheep from the goats (thinking whether some particular variable "safe" or not) he does his first step to disaster. Not to mention that even the wording suggests bulk escaping at the entry point, resembling the very magic quotes feature - already despised, deprecated and removed.

Which is leaving us no choice other than prepared statements.

If you're still not convinced, here is a step-by step explanation I wrote, The Hitchhiker's Guide to SQL Injection protection, where I explained all these matters in detail, and even compiled a section entirely dedicated to bad practices and their disclosure.

editedJan 2 at 13:09

answered Nov 24 '11 at 9:50

Your Common Sense

100k 17 86 155

Great, well thought out article. I might add that using the Sanitize filters of PHP is kind of (but not exactly) a white listing of sorts. For example,FILTER_SANITIZE_NUMBER_INT only allows number characters, thereby white listing characters, not entire strings. In combination with prepared statements, it makes a good "belt and suspenders" approach. – Sablefoste Jan 20 at 17:12

1

@Sablefoste you don't need whitelisting here. Any sanitization will be redundant. Less rules to follow, the less mistakes you will make. Although you could do any validations, do it for sake of your application logic, but not for database. – Your Common Sense Jan 20 at 17:54

add a comment |

up vote 332 down vote

Use PDO and prepared queries.

($conn is a PDO object)

$stmt = $conn->prepare("INSERT INTO tbl VALUES(:id, :name)");
$stmt->bindValue(':id', $id);
$stmt->bindValue(':name', $name);
$stmt->execute();

editedSep 11 '15 at 17:02

casillas

3,892 2 25 44

answered Sep 13 '08 at 13:20

Imran

27.7k 15 68 104

add a comment |

up vote 305 down vote

As you can see, people suggest you to use prepared statements at the most. It's not wrong, but when your query is executedjust once per process, there would be a slightly performance penalty.

I was facing this issue, but I think I solved it in very sophisticated way - the way hackers use to avoid using quotes. I used this in conjuction with emulated prepared statements. I use it to preventall kinds of possible SQL injection attacks.

My approach:

If you expect input to be integer make sure it's really integer. In a variable-type language like PHP it is thisvery important. You can use for example this very simple but powerful solution:sprintf("SELECT 1,2,3 FROM table WHERE 4 = %u", $input);
If you expect anything else from integer hex it. If you hex it, you will perfectly escape all input. In C/C++ there's a function calledmysql_hex_string(), in PHP you can use bin2hex().

Don't worry about that the escaped string will have 2x size of its original length because even if you usemysql_real_escape_string, PHP has to allocate same capacity ((2*input_length)+1), which is the same.
This hex method is often used when you transfer binary data, but I see no reason why not use it on all data to prevent SQL injection attacks. Note that you have to prepend data with0x or use the MySQL function UNHEX instead.

So for example the query:

SELECT password FROM users WHERE name = 'root'

Will become:

SELECT password FROM users WHERE name = 0x726f6f74

or

SELECT password FROM users WHERE name = UNHEX('726f6f74')

Hex is the perfect escape. No way to inject.

Difference between UNHEX function and 0x prefix

There was some discussion in comments, so I finally want to make it clear. These two approaches are very similar, but they are a little different in some ways:

0x prefix can only be used on data columns such as char, varchar, text, block, binary, etc.
Also its use is a little complicated if you are about to insert an empty string. You'll have to entirely replace it with'', or you'll get an error.

UNHEX() works on any column; you do not have to worry about the empty string.

Hex methods are often used as attacks

Note that this hex method is often used as an SQL injection attack where integers are just like strings and escaped just withmysql_real_escape_string. Then you can avoid use of quotes.

For example, if you just do something like this:

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

an attack can inject you very easily. Consider the following injected code returned from your script:

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

and now just extract table structure:

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

And then just select whatever data ones want. Cool isn't it?

But if the coder of injectable site would hex it, no injection would be possible because the query would look like this:SELECT ... WHERE id = UNHEX('2d312075...3635')

editedJul 16 '14 at 2:19

Peter Mortensen

9,354 11 66 98

answered Oct 3 '12 at 14:07

Zaffy

7,640 4 24 56

2

@SumitGupta Yea, you did. MySQL doesnt concatenate with+ but with CONCAT. And to the performance: I dont think it affects performance because mysql has to parse data and it doesnt matter if origin is string or hex – Zaffy Jun 1 '13 at 23:49

2

@YourCommonSense What errors do you encounter? Be specific. – Zaffy Jul 1 '13 at 13:52

5

@YourCommonSense You dont understand the concept... If you want to have string in mysql you quote it like this'root' or you can hex it 0x726f6f74 BUT if you want a number and send it as string you will probably write '42' not CHAR(42) ... '42' in hex would be0x3432 not 0x42 – Zaffy Jul 1 '13 at 14:07

5

@YourCommonSense I have nothing to say... just lol... if you still want to try hex on numeric fields, see second comment. I bet with you that it'll work. – Zaffy Jul 1 '13 at 14:24

3

@YourCommonSense you still dont understand ? You cannot use 0x and concat because if the string is empty you will end with an error. If you want simple alternative to your query try this one SELECT title FROM article WHERE id = UNHEX(' . bin2hex($_GET["id"]) . ') – Zaffy Aug 1 '13 at 12:33

| show 18 more comments

up vote 271 down vote

IMPORTANT

The best way to prevent SQL Injection is to use Prepared Statementsinstead of escaping, as the accepted answer demonstrates.

There are libraries such as Aura.Sql and EasyDB that allow developers to use prepared statements easier. To learn more about why prepared statements are better atstopping SQL injection, refer to this mysql_real_escape_string() bypass and recently fixed Unicode SQL Injection vulnerabilities in WordPress.

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.

mysql_real_escape_string takes 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 details in MySQL - SQL Injection Prevention.

editedJun 1 '15 at 13:45

Scott Arciszewski

8,793 3 24 71

answered Jun 17 '11 at 4:00

rahularyansharma

6,481 8 44 97

18

This is the best you can do with legacy mysql extension. For new code, you're advised to switch to mysqli or PDO. – Álvaro González Feb 26 '13 at 12:42

4

I am not agree with this 'a specially-made function to prevent these attacks'. I think thatmysql_real_escape_string purpose is in allow to build correct SQL query for every input data-string. Prevention sql-injection is the side-effect of this function. – sectus Jul 9 '13 at 5:01

1

you dont use functions to write correct input data-strings. You just write correct ones that don't need escaping or have already been escaped. mysql_real_escape_string() may have been designed with the purpose you mention in mind, but its only value is preventing injection. – Nazca Mar 12 '14 at 22:38

7

WARNING!mysql_real_escape_string() is not infallible. – eggyal Apr 25 '14 at 14:50

3

mysql_real_escape_string is now deprecated, so its no longer a viable option. It will be removed in the future from PHP. Its best to move onto what the PHP or MySQL folks recommend. – jww Apr 8 '15 at 6:41

| show 3 more comments

up vote 220 down vote

Security Warning: This answer is not in line with security best practices.Escaping is inadequate to prevent SQL injection, use prepared statements instead. Use the strategy outlined below at your own risk. (Also,mysql_real_escape_string() was removed in PHP 7.)

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

editedJan 2 at 3:47

Scott Arciszewski

8,793 3 24 71

answered Sep 13 '08 at 0:15

Tanerax

3,879 4 20 24

13

I have tried your example and it's work fine for me.Could you clear "this won't solve every problem" – Chinook Apr 22 '12 at 20:31

6

If you don't quote the string, it's still injectable. Take$q = "SELECT col FROM tbl WHERE x = $safe_var"; for example. Setting$safe_var to 1 UNION SELECT password FROM users works in this case because of the lack of quotes. It's also possible to inject strings into the query usingCONCAT and CHR. – Polynomial Apr 16 '13 at 18:06

5

@glglgl That's fine, if you're willing to accept the risk of someone forgetting a quote somewhere in your entire application. Bad development practices keep me in a job ;) – Polynomial Jul 10 '13 at 9:05

6

WARNING!mysql_real_escape_string() is not infallible. – eggyal Apr 25 '14 at 14:46

1

mysql_real_escape_string is now deprecated, so its no longer a viable option. It will be removed in the future from PHP. Its best to move onto what the PHP or MySQL folks recommend. – jww Apr 8 '15 at 6:37

| show 4 more comments

up vote 207 down vote

Whatever you do end up using, make sure that you check your input hasn't already been mangled bymagic_quotes or some other well-meaning rubbish, and if necessary, run it throughstripslashes or whatever to sanitise it.

answered Dec 8 '08 at 5:26

Rob

37.1k 2 53 84

7

Indeed; running with magic_quotes switched on just encourages poor practice. However, sometimes you can't always control the environment to that level - either you don't have access to manage the server, or your application has to coexist with applications that (shudder) depend on such configuration. For these reasons, it's good to write portable applications - though obviously the effort is wasted if you do control the deployment environment, e.g. because it's an in-house application, or only going to be used in your specific environment. – Rob Apr 24 '11 at 17:04

13

As of PHP 5.4, the abomination known as 'magic quotes' has beenkilled dead. And good riddance to bad rubbish. – BryanH Jan 16 '13 at 22:45

add a comment |

up vote 202 down vote

Parameterized query AND input validation is the way to go. There is many scenarios under which SQL injection may occur, even thoughmysql_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 cases, you can't use ' to protect the encapsulation.

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

editedMay 30 '14 at 18:33

Peter Mortensen

9,354 11 66 98

answered Jul 3 '11 at 21:50

Cedric

2,301 1 11 11

Dont take user inputs where not neccessary. sanitize and filter user inputs before using it for database querying. Use the whitelist approach it easier to create white list rather than blacklist read about it here. schneier.com/blog/archives/2011/01/whitelisting_vs.html – Danyal Fayyaz Sep 2 '15 at 11:58

You can prevent SQL injection if you adopt an input validation technique in which user input is authenticated against a set of defined rules for length, type and syntax and also against business rules. – Josip Ivic Sep 15 '15 at 8:07

add a comment |

up vote 167 down vote

In my opinion, the best way to generally prevent SQL injection in your PHP application (or any web application, 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 application in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.

editedJul 16 '14 at 2:05

Peter Mortensen

9,354 11 66 98

answered Jul 3 '12 at 10:14

Johannes Fahrenkrug

20.1k 7 67 103

add a comment |

up vote 155 down vote

There are many ways of preventing SQL injections and other SQL hacks. You can easily find it on the Internet (Google Search). Of coursePDO 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.

editedSep 17 '12 at 22:12

Peter Mortensen

9,354 11 66 98

answered Jul 23 '12 at 10:19

Manish Shrivastava

9,132 5 54 75

add a comment |

up vote 151 down vote

I favor stored procedures (MySQL has had stored procedures support since 5.0) from a security point of view - the advantages are -

Most databases (including MySQL) enable user access to be restricted to executing stored procedures. The fine grained security access control is useful to prevent escalation of privileges attacks. This prevents compromised applications from being able to run SQL directly against the database.
They abstract the raw SQL query from the application so less information of the database structure is available to the application. This makes it harder for people to understand the underlying structure of the database and design suitable attacks.
They accept only parameters, so the advantages of parameterized queries are there. Of course - IMO you still need to sanitize your input - especially if you are using dynamic SQL inside the stored procedure.

The disadvantages are -

They (stored procedures) are tough to maintain and tend to multiply very quickly. This makes managing them an issue.
They are not very suitable for dynamic queries - if they are built to accept dynamic code as parameters then a lot of the advantages are negated.

editedMay 30 '14 at 18:29

Peter Mortensen

9,354 11 66 98

answered Nov 3 '09 at 18:05

Nikhil

2,787 2 14 25

add a comment |

up vote 148 down vote

I think if someone wants to use PHP and MySQL or some other dataBase server:

Think about learning PDO (PHP Data Objects) – it is a database access layer providing a uniform method of access to multiple databases.
Think about learning MySQLi
Use native PHP functions like: strip_tags, mysql_real_escape_string or if variable numeric, just (int)$foo. Read more about type of variables in PHPhere. If you're using libraries such as PDO or MySQLi, always usePDO::quote() andmysqli_real_escape_string().

Libraries examples:

---- PDO

----- No placeholders - ripe for SQL injection! It's bad

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values ($name, $addr, $city)");

----- Unnamed placeholders

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values (?, ?, ?);

----- Named placeholders

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) value (:name, :addr, :city)");

--- MySQLi

$request = $mysqliConnection->prepare('
       SELECT * FROM trainers
       WHERE name = ?
       AND email = ?
       AND last_login > ?');

    $query->bind_param('first_param', 'second_param', $mail, time() - 3600);
    $query->execute();

P.S:

PDO wins this battle with ease. With support for twelve different database drivers and named parameters, we can ignore the small performance loss, and get used to its API. From a security standpoint, both of them are safe as long as the developer uses them the way they are supposed to be used

But while both PDO and MySQLi are quite fast, MySQLi performs insignificantly faster in benchmarks – ~2.5% for non-prepared statements, and ~6.5% for prepared ones.

And please test every query to your database - it's a better way to prevent injection.

editedJul 16 '14 at 2:27

Peter Mortensen

9,354 11 66 98

answered Oct 10 '12 at 14:53

imRcH

3,235 1 12 21

add a comment |

up vote 131 down vote

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 . "')");

answered Jun 12 '12 at 8:03

devOp

2,127 1 6 20

add a comment |

up vote 126 down vote

If you want to take advantage of cache engines, like Redis or Memcached, maybe DALMP could be a choice. It uses pureMySQLi. Check this:DALMP Database Abstraction Layer for MySQL using PHP.

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.DALMP Database Abstraction Layer for MySQL using PHP.

editedJul 16 '14 at 2:31

Peter Mortensen

9,354 11 66 98

answered Oct 15 '12 at 13:52

nbari

1,693 1 8 16

add a comment |

up vote 122 down vote

For those unsure of how to use PDO (coming from the mysql_ functions), I made avery, 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 formatyou 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));

answered Sep 19 '12 at 18:10

Xeoncross

18.9k 33 159 257

add a comment |

up vote 109 down vote

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

editedAug 4 '15 at 14:38

Ram Sharma

6,186 3 15 31

answered Aug 3 '12 at 19:59

Nicolas Finelli

1,314 1 6 6

add a comment |

up vote 106 down vote

A few guidelines for escaping special characters in SQL statements.

Don't use MySQL, this extension is deprecated, use MySQLi or PDO.

MySQLi

For manually escaping special characters in a string you can use the mysqli_real_escape_string function. The function will not work properly unless the correct character set is set withmysqli_set_charset.

Example:

$mysqli = new mysqli( 'host', 'user', 'password', 'database' );
$mysqli->set_charset( 'charset');

$string = $mysqli->real_escape_string( $string );
$mysqli->query( "INSERT INTO table (column) VALUES ('$string')" );

For automatic escaping of values with prepared statements, use mysqli_prepare, and mysqli_stmt_bind_param where types for the corresponding bind variables must be provided for an appropriate conversion:

Example:

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

$stmt->bind_param( "is", $integer, $string );

$stmt->execute();

No matter if you use prepared statements or mysqli_real_escape_string, you always have to know the type of input data you're working with.

So if you use a prepared statement, you must specify the types of the variables for mysqli_stmt_bind_param function.

And use of mysqli_real_escape_string is for, as the name says, escaping special characters in a string, so it will not make integers safe. The purpose of this function is to prevent breaking the strings in SQL statements, and the damage to the database that it could cause. mysqli_real_escape_string is a useful function when used properly, especially when combined with sprintf.

Example:

$string = "x' OR name LIKE '%John%";
$integer = '5 OR id != 0';

$query = sprintf( "SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string( $string ), $integer );

echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 5

$integer = '99999999999999999999';
$query = sprintf( "SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string( $string ), $integer );

echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 2147483647

editedJul 16 '14 at 2:47

community wiki

3 revs, 2 users 88%
Danijel

add a comment |

up vote 101 down vote

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

Use of mysql_real_escape_string(), which is a pre-defined function inPHP, 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.
The most advanced way is to use PDOs.

I hope this will help you.

Consider the following query:

$iId = mysql_real_escape_string("1 OR 1=1"); $sSql = "SELECT * FROM table WHERE id = $iId";

mysql_real_escape_string() will not protect here. If you use single quotes (' ') around your variables inside your query is what protects you against this. Here is an solution below for this:

$iId = (int) mysql_real_escape_string("1 OR 1=1"); $sSql = "SELECT * FROM table WHERE id = $iId";

This question has some good answers about this.

I suggest, using PDO is the best option.

Edit:

mysql_real_escape_string() is deprecated as of PHP 5.5.0. Use either mysqli or PDO.

An alternative to mysql_real_escape_string() is

string mysqli_real_escape_string ( mysqli $link , string $escapestr )

Example:

$iId = $mysqli->real_escape_string("1 OR 1=1");
$mysqli->query("SELECT * FROM table WHERE id = $iId");

editedApr 22 '15 at 12:13

answered Sep 14 '12 at 14:37

Soumalya Banerjee

1,422 1 11 22

add a comment |

up vote 92 down vote

Regarding to many useful answers, I hope to add some values to this thread. SQL injection is type of attack that can be done through user inputs (Inputs that filled by user and then used inside queries), The SQL injection patterns are correct query syntax while we can call it: bad queries for bad reasons, we assume that there might be bad person that try to get secret information (by passing access control) that affect the three principles of security (Confidentiality, Integrity, Availability).

Now, our point is to prevent security threats such as SQL injection attacks, the question asking (How to prevent SQL injection attack using PHP), be more realistic, data filtering or clearing input data is the case when using user-input data inside such query, using PHP or any other programming language is not the case, or as recommended by more people to use modern technology such as prepared statement or any other tools that currently supporting SQL injection prevention, consider that these tools not available anymore? how you secure your application?

My approach against SQL injection is: clearing user-input data before sending it to database (before using it inside any query).

Data filtering for (Converting unsafe data to safe data) Consider thatPDO and MySQLi not available, how can you secure your application? do you force me to use them? what about other languages other than PHP? I prefer to provide general ideas as it can be used for wider border not just for specific language.

SQL user (limiting user privilege): most common SQL operations are (SELECT, UPDATE, INSERT), then, why giving UPDATE privilege to a user that not require it? for example:login, and search pages are only using SELECT, then, why using db users in these pages with high privileges?RULE: do not create one database user for all privileges, for all SQL operations, you can create your scheme like (deluser, selectuser, updateuser) as usernames for easy usage.

see Principle of least privilege

Data filtering: before building any query user input should be validated and filtered, for programmers, it's important to define some properties for each user-input variables:data type, data pattern, and data length. a field that is a number between (x and y) must be exactly validated using exact rule, for a field that is a string (text): pattern is the case, for example: username must contain only some characters lets say [a-zA-Z0-9_-.] the length vary between (x and n) where x and n (integers, x <=n ).Rule: creating exact filters and validation rules are best practice for me.
Use other tools: Here, I will also agree with you that prepared statement (parametrized query) and Stored procedures, the disadvantages here is these ways requires advanced skills which are not exist in most users, the basic idea here is to distinguish between SQL query and the data that being used inside, both approach can be used even with unsafe data, because the user-input data here not add anything to the original query such as (any or x=x). for more information please readOWASP SQL Injection Prevention Cheat Sheet.

Now, if you are an advanced user, start using these defense as you like, but, for beginners, if they can't quickly implement stored procedure and prepared statement, it's better to filter input data as much they can.

Finally, let's consider that user sends this text below instead of entering his username:

[1] UNION SELECT IF(SUBSTRING(Password,1,1)='2',BENCHMARK(100000,SHA1(1)),0) User,Password FROM mysql.user WHERE User = 'root'

This input can be checked early without any prepared statement and stored procedures, but to be on safe side, using them starts after user-data filtering and validation.

Last point is detecting unexpected behavior which requires more effort and complexity, it's not recommended for normal web applications. Unexpected behavior in above user input is: SELECT, UNION, IF, SUBSTRING, BENCHMARK, SHA, root once these words detected, you can avoid the input.

UPDATE1:

A user commented that this post is useless, OK! Here is what OWASP.ORG provided:

Primary defenses:

Option #1: Use of Prepared Statements (Parameterized Queries)
Option #2: Use of Stored Procedures
Option #3: Escaping all User Supplied Input

Additional defenses:

Also Enforce: Least Privilege
Also Perform: White List Input Validation

As you may knew, claiming on any article should be supported by valid argument, at least one reference! Otherwise it's considered as attack and bad claim!

Update2:

From the PHP manual, PHP: Prepared Statements - Manual:

Escaping and SQL injection

Bound variables will be escaped automatically by the server. The server inserts their escaped values at the appropriate places into the statement template before execution. A hint must be provided to the server for the type of bound variable, to create an appropriate conversion. See the mysqli_stmt_bind_param() function for more information.

The automatic escaping of values within the server is sometimes considered a security feature to prevent SQL injection. The same degree of security can be achieved with non-prepared statements, if input values are escaped correctly.

Update3:

I created test cases for knowing how PDO and MySQLi sends the query to MySQL server when using prepared statement:

PDO:

$user = "''1''"; //Malicious keyword
$sql = 'SELECT * FROM awa_user WHERE userame =:username';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':username' => $user));

Query Log:

    189 Query SELECT * FROM awa_user WHERE userame ='\'\'1\'\''
    189 Quit

MySQLi:

$stmt = $mysqli->prepare("SELECT * FROM awa_user WHERE username =?")) {
$stmt->bind_param("s", $user);
$user = "''1''";
$stmt->execute();

Query Log:

    188 Prepare   SELECT * FROM awa_user WHERE username =?
    188 Execute   SELECT * FROM awa_user WHERE username ='\'\'1\'\''
    188 Quit

It's clear that a prepared statement is also escaping the data, nothing else.

As also mentioned in above statement The automatic escaping of values within the server is sometimes considered a security feature to prevent SQL injection. The same degree of security can be achieved with non-prepared statements, if input values are escaped correctly, therefore, this proves that data validation such as intval() is a good idea for integer values before sending any query, in addition, preventing malicious user data before sending the query iscorrect and valid approach.

Please see this question for more detail: PDO sends raw query to MySQL while Mysqli sends prepared query, both produce the same result

References:

editedMar 24 '14 at 21:05

community wiki

11 revs, 4 users 73%
user1646111

add a comment |

up vote 89 down vote

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 information about flush.

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

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

Learn more about GRANT.

editedJul 23 '14 at 9:17

answered Oct 25 '12 at 8:07

apurv nerlekar

1,236 10 17

add a comment |

up vote 77 down vote

A simple way would be to use a PHP framework like CodeIgniter or Laravel which have in-built features like filtering and active-record, so that you don't have to worry about these nuances.

editedJul 16 '14 at 2:48

community wiki

2 revs, 2 users 60%
Peter Mortensen

add a comment |

up vote 70 down vote

Warning : the approach described in this answer only applies for very specific scenarios and isn't secure since SQL injection attacks do not only rely on being able to injectX=Y.

If the attackers are trying to hack with the form via PHP's $_GET variable or with the URL's query string, you would be able to catch them if they're not secure.

RewriteCond %{QUERY_STRING} ([0-9]+)=([0-9]+)
RewriteRule ^(.*) ^/track.php

Because 1=1, 2=2, 1=2, 2=1,1+1=2, etc... are the common questions to an SQL database of an attacker. Maybe also it's used by many hacking applications.

But you must be careful, that you must not rewrite a safe query from your site. The code above is giving you a tip, to rewrite or redirect(it depends on you) that hacking-specific dynamic query string into a page that will store the attacker'sIP address, or EVEN THEIR COOKIES, history, browser, or any other sensitive information, so you can deal with them later by banning their account or contacting authorities.

editedJan 20 '15 at 7:33

community wiki

5 revs, 3 users 61%
Servant

add a comment |

up vote 64 down vote

There are so many answers for PHP and MySQL, but here is code forPHP and Oracle for preventing SQL injection as well as regular use of oci8 drivers:

$c = oci_connect($userName, $password, "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =$serverName)(PORT = 1521)))(CONNECT_DATA=(SID=$databaseName)))");
$strQuery = "UPDATE table SET field = :xx WHERE ID = 123"
$stmt = OCIParse($c, $strQuery);

OCIBindByName($stmt, ':xx', $fieldval);

$ok = OCIExecute($stmt);

editedJul 16 '14 at 2:53

community wiki

3 revs, 3 users 69%
Chintan Gor

add a comment |

up vote 59 down vote

Using PDO and MYSQLi is a good practice to prevent SQL injections, but if you really want to work with MySQL functions and queries, it would be better to use

mysql_real_escape_string

$unsafe_variable = mysql_real_escape_string($_POST['user_input']);

There are more ability to prevent this: like identify - if the input is a string, number, char or array, there are so many inbuilt functions to detect this. Also it would be better to use these functions to check input data.

is_string

$unsafe_variable = (is_string($_POST['user_input']) ? $_POST['user_input'] : '');

is_numeric

$unsafe_variable = (is_numeric($_POST['user_input']) ? $_POST['user_input'] : '');

And it is so much better to use those functions to check input data with mysql_real_escape_string.

editedJul 16 '14 at 2:51

community wiki

2 revs, 2 users 87%
Rakesh Sharma

5

Also, there is absolutely no point in checking $_POST array members with is_string() – Your Common Sense Jan 18 '14 at 7:06

9

WARNING!mysql_real_escape_string() is not infallible. – eggyal Apr 25 '14 at 14:54

1

@YourCommonSense you need to believe so what you want to update – Rakesh Sharma May 8 '14 at 8:38

3

mysql_real_escape_string is now deprecated, so its no longer a viable option. It will be removed from PHP in the future. Its best to move onto what the PHP or MySQL folks recommend. – jww Apr 8 '15 at 6:53

add a comment |

up vote 37 down vote

A good idea is to use an 'object-relational mapper' like Idiorm:

$user = ORM::for_table('user')
->where_equal('username', 'j4mie')
->find_one();

$user->first_name = 'Jamie';
$user->save();

$tweets = ORM::for_table('tweet')
    ->select('tweet.*')
    ->join('user', array(
        'user.id', '=', 'tweet.user_id'
    ))
    ->where_equal('user.username', 'j4mie')
    ->find_many();

foreach ($tweets as $tweet) {
    echo $tweet->text;
}

It not only saves you from SQL injections, but from syntax errors too!

editedMar 24 '14 at 20:56

community wiki

2 revs, 2 users 96%
Thomas Ahle

add a comment |

up vote 31 down vote

I've written this little function several years ago:

function sqlvprintf($query, $args)
{
    global $DB_LINK;
    $ctr = 0;
    ensureConnection(); // Connect to database if not connected already.
    $values = array();
    foreach ($args as $value)
    {
        if (is_string($value))
        {
            $value = "'" . mysqli_real_escape_string($DB_LINK, $value) . "'";
        }
        else if (is_null($value))
        {
            $value = 'NULL';
        }
        else if (!is_int($value) && !is_float($value))
        {
            die('Only numeric, string, array and NULL arguments allowed in a query. Argument '.($ctr+1).' is not a basic type, it\'s type is '. gettype($value). '.');
        }
        $values[] = $value;
        $ctr++;
    }
    $query = preg_replace_callback(
        '/{(\\d+)}/', 
        function($match) use ($values)
        {
            if (isset($values[$match[1]]))
            {
                return $values[$match[1]];
            }
            else
            {
                return $match[0];
            }
        },
        $query
    );
    return $query;
}

function runEscapedQuery($preparedQuery /*, ...*/)
{
    $params = array_slice(func_get_args(), 1);
    $results = runQuery(sqlvprintf($preparedQuery, $params)); // Run query and fetch results.   
    return $results;
}

This allows running statements in an one-liner C#-ish String.Format like:

runEscapedQuery("INSERT INTO Whatever (id, foo, bar) VALUES ({0}, {1}, {2})", $numericVar, $stringVar1, $stringVar2);

It escapes considering the variable type. If you try to parameterize table, column names, it would fail as it puts every string in quotes which is invalid syntax.

SECURITY UPDATE: The previous str_replace version allowed injections by adding {#} tokens into user data. Thispreg_replace_callback version doesn't cause problems if the replacement contains these tokens.

editedJun 29 '14 at 22:30

community wiki

3 revs
Calmarius

add a comment |

up vote 5105 down vote accepted

+50

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:

Using PDO (for any supported database driver):

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute(array('name' => $name));

foreach ($stmt as $row) {
    // do something with $row
}

Using MySQLi (for MySQL):

$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
}

If you're connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (e.g.pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

Correctly setting up the connection

Note that when using PDO to access a MySQL database real prepared statements arenot 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 aFatal Error when something goes wrong. And it gives the developer the chance tocatch any error(s) which are thrown as PDOExceptions.

What is mandatory however is the first setAttribute() line, which tells PDO to disable emulated prepared statements and usereal prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to 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 callexecute, 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 an 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));

Can Prepared Statements Be Used For Dynamic Queries?

While you can still use prepared statements for the query parameters, the structure of the dynamic query itself cannot be parametrized and certain query features cannot be parametrized.

For these specific scenarios, the best thing to do is use a whitelist filter that restricts the possible values.

// Value whitelist
  // $dir can only be 'DESC' or 'ASC'
$dir = !empty($direction) ? 'DESC' : 'ASC';

editedSep 26 '15 at 5:54

Your Common Sense

100k 17 86 155

answered Sep 13 '08 at 12:30

Theo

87.8k 8 98 132

Just to add because I didn't see it anywhere else here, another line of defense is aweb application firewall (WAF) that can have rules be set to look for sql injection attacks: – jkerak Dec 29 '15 at 20:11

I woudl like to suggest use redbeanphp orm which is built on pdo. if you use pdo directly,it is really painful. – jewelhuq Jan 2 at 14:24

To learn more about 'PHP Data Objects' i.e. PDO visite these very good articals : 1.code.tutsplus.com/tutorials/… 2.code.tutsplus.com/tutorials/… – rakesh Jan 5 at 11:23

1

Also, the official documentation of mysql_query only allows to execute one query, so any other query besides ; is ignored. Even if this is already deprecated there are a lot of systems under PHP 5.5.0 and that may use this function. php.net/manual/en/function.mysql-query.php – Randall Valenciano Jan 19 at 17:40

2

I disagree with the practice of constantly wrapping every PDO statement in a try/catch. Fatal errors can be good. What's the point of just echo'ing out the errormessage and resuming an erroneous script, when not catching the exception gives you a nice stack trace? – Hugo Zink Feb 11 at 8:03

| show 10 more comments

up vote 958 down vote

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.

Warning:

As of PHP 5.5.0 mysql_real_escape_string and the mysql extension aredeprecated. Please use mysqli extension and mysqli::escape_string function instead

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

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 bymysql_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 byPolynomial 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.

editedMar 31 '14 at 21:44

Denys Vitali

356 3 16

answered Sep 13 '08 at 9:48

Matt Sheppard

46.3k 37 92 110

add a comment |

up vote 487 down vote

I'd recommend using PDO (PHP Data Objects) to run parameterized SQL queries.

Not only does this protect against SQL injection, it also speeds up queries.

And by using PDO rather than mysql_, mysqli_, and pgsql_ functions, you make your app a little more abstracted from the database, in the rare occurrence that you have to switch database providers.

editedMar 7 '14 at 6:12

Amarnath Balasubramanian

3,822 5 16 40

answered Sep 13 '08 at 0:02

Kibbee

43.1k 24 116 159

add a comment |

up vote 471 down vote

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 white-listing seems the only approach in this case.

Update

Although there is a general agreement on the best practices regarding SQL injection protection, there arestill many bad practices as well. And some of them too deeply rooted in the minds of PHP users. For instance, on this very page there are (although invisible to most visitors)more than 80 deleted answers - all removed by the community due to bad quality or promoting bad and outdated practices. Worse yet, some of bad answers aren't deleted but rather prospering.

For example, there are still(1) many(2) answers(3)suggesting(4)you(5) manual string escaping - an outdated approach that is proven to be insecure.

Or there is a slightly better answer that suggests just another method of string formatting and even boasts it as ultimate panacea. While of course it is not. This method is no better than regular string formatting yet it keeps all its drawbacks: it is applicable to strings only and, as any other manual formatting, it's essentially optional, not obligatory measure, prone to human error of any sort.

I think that all this because of one very old superstition, supported by such authorities likeOWASP or PHP manual, which proclaims equality between whatever "escaping" and protection from SQL injections.

Despite of what PHP manual said for ages, *_escape_string by no means makes data safe, and never has been intended to. Beside of being useless for any SQL part other than string, manual escaping is wrong because it is manual as opposite to automated.

And OWASP makes it even worse, stressing on escaping user input which is an utter nonsense: there should be no such words in the context of injection protection. Every variable is potentially dangerous - no matter of the source! Or, in other words - every variable have to be properly formatted to be put into query - no matter of the source again. It's destination that matters. The moment a developer starts to separate the sheep from the goats (thinking whether some particular variable "safe" or not) he does his first step to disaster. Not to mention that even the wording suggests bulk escaping at the entry point, resembling the very magic quotes feature - already despised, deprecated and removed.

Which is leaving us no choice other than prepared statements.

If you're still not convinced, here is a step-by step explanation I wrote, The Hitchhiker's Guide to SQL Injection protection, where I explained all these matters in detail, and even compiled a section entirely dedicated to bad practices and their disclosure.

editedJan 2 at 13:09

answered Nov 24 '11 at 9:50

Your Common Sense

100k 17 86 155

Great, well thought out article. I might add that using the Sanitize filters of PHP is kind of (but not exactly) a white listing of sorts. For example,FILTER_SANITIZE_NUMBER_INT only allows number characters, thereby white listing characters, not entire strings. In combination with prepared statements, it makes a good "belt and suspenders" approach. – Sablefoste Jan 20 at 17:12

1

@Sablefoste you don't need whitelisting here. Any sanitization will be redundant. Less rules to follow, the less mistakes you will make. Although you could do any validations, do it for sake of your application logic, but not for database. – Your Common Sense Jan 20 at 17:54

add a comment |

up vote 332 down vote

Use PDO and prepared queries.

($conn is a PDO object)

$stmt = $conn->prepare("INSERT INTO tbl VALUES(:id, :name)");
$stmt->bindValue(':id', $id);
$stmt->bindValue(':name', $name);
$stmt->execute();

editedSep 11 '15 at 17:02

casillas

3,892 2 25 44

answered Sep 13 '08 at 13:20

Imran

27.7k 15 68 104

add a comment |

up vote 305 down vote

As you can see, people suggest you to use prepared statements at the most. It's not wrong, but when your query is executedjust once per process, there would be a slightly performance penalty.

I was facing this issue, but I think I solved it in very sophisticated way - the way hackers use to avoid using quotes. I used this in conjuction with emulated prepared statements. I use it to preventall kinds of possible SQL injection attacks.

My approach:

If you expect input to be integer make sure it's really integer. In a variable-type language like PHP it is thisvery important. You can use for example this very simple but powerful solution:sprintf("SELECT 1,2,3 FROM table WHERE 4 = %u", $input);
If you expect anything else from integer hex it. If you hex it, you will perfectly escape all input. In C/C++ there's a function calledmysql_hex_string(), in PHP you can use bin2hex().

Don't worry about that the escaped string will have 2x size of its original length because even if you usemysql_real_escape_string, PHP has to allocate same capacity ((2*input_length)+1), which is the same.
This hex method is often used when you transfer binary data, but I see no reason why not use it on all data to prevent SQL injection attacks. Note that you have to prepend data with0x or use the MySQL function UNHEX instead.

So for example the query:

SELECT password FROM users WHERE name = 'root'

Will become:

SELECT password FROM users WHERE name = 0x726f6f74

or

SELECT password FROM users WHERE name = UNHEX('726f6f74')

Hex is the perfect escape. No way to inject.

Difference between UNHEX function and 0x prefix

There was some discussion in comments, so I finally want to make it clear. These two approaches are very similar, but they are a little different in some ways:

0x prefix can only be used on data columns such as char, varchar, text, block, binary, etc.
Also its use is a little complicated if you are about to insert an empty string. You'll have to entirely replace it with'', or you'll get an error.

UNHEX() works on any column; you do not have to worry about the empty string.

Hex methods are often used as attacks

Note that this hex method is often used as an SQL injection attack where integers are just like strings and escaped just withmysql_real_escape_string. Then you can avoid use of quotes.

For example, if you just do something like this:

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

an attack can inject you very easily. Consider the following injected code returned from your script:

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

and now just extract table structure:

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

And then just select whatever data ones want. Cool isn't it?

But if the coder of injectable site would hex it, no injection would be possible because the query would look like this:SELECT ... WHERE id = UNHEX('2d312075...3635')

editedJul 16 '14 at 2:19

Peter Mortensen

9,354 11 66 98

answered Oct 3 '12 at 14:07

Zaffy

7,640 4 24 56

2

@SumitGupta Yea, you did. MySQL doesnt concatenate with+ but with CONCAT. And to the performance: I dont think it affects performance because mysql has to parse data and it doesnt matter if origin is string or hex – Zaffy Jun 1 '13 at 23:49

2

@YourCommonSense What errors do you encounter? Be specific. – Zaffy Jul 1 '13 at 13:52

5

@YourCommonSense You dont understand the concept... If you want to have string in mysql you quote it like this'root' or you can hex it 0x726f6f74 BUT if you want a number and send it as string you will probably write '42' not CHAR(42) ... '42' in hex would be0x3432 not 0x42 – Zaffy Jul 1 '13 at 14:07

5

@YourCommonSense I have nothing to say... just lol... if you still want to try hex on numeric fields, see second comment. I bet with you that it'll work. – Zaffy Jul 1 '13 at 14:24

3

@YourCommonSense you still dont understand ? You cannot use 0x and concat because if the string is empty you will end with an error. If you want simple alternative to your query try this one SELECT title FROM article WHERE id = UNHEX(' . bin2hex($_GET["id"]) . ') – Zaffy Aug 1 '13 at 12:33

| show 18 more comments

up vote 271 down vote

IMPORTANT

The best way to prevent SQL Injection is to use Prepared Statementsinstead of escaping, as the accepted answer demonstrates.

There are libraries such as Aura.Sql and EasyDB that allow developers to use prepared statements easier. To learn more about why prepared statements are better atstopping SQL injection, refer to this mysql_real_escape_string() bypass and recently fixed Unicode SQL Injection vulnerabilities in WordPress.

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.

mysql_real_escape_string takes 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 details in MySQL - SQL Injection Prevention.

editedJun 1 '15 at 13:45

Scott Arciszewski

8,793 3 24 71

answered Jun 17 '11 at 4:00

rahularyansharma

6,481 8 44 97

18

This is the best you can do with legacy mysql extension. For new code, you're advised to switch to mysqli or PDO. – Álvaro González Feb 26 '13 at 12:42

4

I am not agree with this 'a specially-made function to prevent these attacks'. I think thatmysql_real_escape_string purpose is in allow to build correct SQL query for every input data-string. Prevention sql-injection is the side-effect of this function. – sectus Jul 9 '13 at 5:01

1

you dont use functions to write correct input data-strings. You just write correct ones that don't need escaping or have already been escaped. mysql_real_escape_string() may have been designed with the purpose you mention in mind, but its only value is preventing injection. – Nazca Mar 12 '14 at 22:38

7

WARNING!mysql_real_escape_string() is not infallible. – eggyal Apr 25 '14 at 14:50

3

mysql_real_escape_string is now deprecated, so its no longer a viable option. It will be removed in the future from PHP. Its best to move onto what the PHP or MySQL folks recommend. – jww Apr 8 '15 at 6:41

| show 3 more comments

up vote 220 down vote

Security Warning: This answer is not in line with security best practices.Escaping is inadequate to prevent SQL injection, use prepared statements instead. Use the strategy outlined below at your own risk. (Also,mysql_real_escape_string() was removed in PHP 7.)

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

editedJan 2 at 3:47

Scott Arciszewski

8,793 3 24 71

answered Sep 13 '08 at 0:15

Tanerax

3,879 4 20 24

13

I have tried your example and it's work fine for me.Could you clear "this won't solve every problem" – Chinook Apr 22 '12 at 20:31

6

If you don't quote the string, it's still injectable. Take$q = "SELECT col FROM tbl WHERE x = $safe_var"; for example. Setting$safe_var to 1 UNION SELECT password FROM users works in this case because of the lack of quotes. It's also possible to inject strings into the query usingCONCAT and CHR. – Polynomial Apr 16 '13 at 18:06

5

@glglgl That's fine, if you're willing to accept the risk of someone forgetting a quote somewhere in your entire application. Bad development practices keep me in a job ;) – Polynomial Jul 10 '13 at 9:05

6

WARNING!mysql_real_escape_string() is not infallible. – eggyal Apr 25 '14 at 14:46

1

mysql_real_escape_string is now deprecated, so its no longer a viable option. It will be removed in the future from PHP. Its best to move onto what the PHP or MySQL folks recommend. – jww Apr 8 '15 at 6:37

| show 4 more comments

up vote 207 down vote

Whatever you do end up using, make sure that you check your input hasn't already been mangled bymagic_quotes or some other well-meaning rubbish, and if necessary, run it throughstripslashes or whatever to sanitise it.

answered Dec 8 '08 at 5:26

Rob

37.1k 2 53 84

7

Indeed; running with magic_quotes switched on just encourages poor practice. However, sometimes you can't always control the environment to that level - either you don't have access to manage the server, or your application has to coexist with applications that (shudder) depend on such configuration. For these reasons, it's good to write portable applications - though obviously the effort is wasted if you do control the deployment environment, e.g. because it's an in-house application, or only going to be used in your specific environment. – Rob Apr 24 '11 at 17:04

13

As of PHP 5.4, the abomination known as 'magic quotes' has beenkilled dead. And good riddance to bad rubbish. – BryanH Jan 16 '13 at 22:45

add a comment |

up vote 202 down vote

Parameterized query AND input validation is the way to go. There is many scenarios under which SQL injection may occur, even thoughmysql_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 cases, you can't use ' to protect the encapsulation.

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

editedMay 30 '14 at 18:33

Peter Mortensen

9,354 11 66 98

answered Jul 3 '11 at 21:50

Cedric

2,301 1 11 11

Dont take user inputs where not neccessary. sanitize and filter user inputs before using it for database querying. Use the whitelist approach it easier to create white list rather than blacklist read about it here. schneier.com/blog/archives/2011/01/whitelisting_vs.html – Danyal Fayyaz Sep 2 '15 at 11:58

You can prevent SQL injection if you adopt an input validation technique in which user input is authenticated against a set of defined rules for length, type and syntax and also against business rules. – Josip Ivic Sep 15 '15 at 8:07

add a comment |

up vote 167 down vote

In my opinion, the best way to generally prevent SQL injection in your PHP application (or any web application, 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 application in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.

editedJul 16 '14 at 2:05

Peter Mortensen

9,354 11 66 98

answered Jul 3 '12 at 10:14

Johannes Fahrenkrug

20.1k 7 67 103

add a comment |

up vote 155 down vote

There are many ways of preventing SQL injections and other SQL hacks. You can easily find it on the Internet (Google Search). Of coursePDO 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.

editedSep 17 '12 at 22:12

Peter Mortensen

9,354 11 66 98

answered Jul 23 '12 at 10:19

Manish Shrivastava

9,132 5 54 75

add a comment |

up vote 151 down vote

I favor stored procedures (MySQL has had stored procedures support since 5.0) from a security point of view - the advantages are -

Most databases (including MySQL) enable user access to be restricted to executing stored procedures. The fine grained security access control is useful to prevent escalation of privileges attacks. This prevents compromised applications from being able to run SQL directly against the database.
They abstract the raw SQL query from the application so less information of the database structure is available to the application. This makes it harder for people to understand the underlying structure of the database and design suitable attacks.
They accept only parameters, so the advantages of parameterized queries are there. Of course - IMO you still need to sanitize your input - especially if you are using dynamic SQL inside the stored procedure.

The disadvantages are -

They (stored procedures) are tough to maintain and tend to multiply very quickly. This makes managing them an issue.
They are not very suitable for dynamic queries - if they are built to accept dynamic code as parameters then a lot of the advantages are negated.

editedMay 30 '14 at 18:29

Peter Mortensen

9,354 11 66 98

answered Nov 3 '09 at 18:05

Nikhil

2,787 2 14 25

add a comment |

up vote 148 down vote

I think if someone wants to use PHP and MySQL or some other dataBase server:

Think about learning PDO (PHP Data Objects) – it is a database access layer providing a uniform method of access to multiple databases.
Think about learning MySQLi
Use native PHP functions like: strip_tags, mysql_real_escape_string or if variable numeric, just (int)$foo. Read more about type of variables in PHPhere. If you're using libraries such as PDO or MySQLi, always usePDO::quote() andmysqli_real_escape_string().

Libraries examples:

---- PDO

----- No placeholders - ripe for SQL injection! It's bad

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values ($name, $addr, $city)");

----- Unnamed placeholders

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values (?, ?, ?);

----- Named placeholders

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) value (:name, :addr, :city)");

--- MySQLi

$request = $mysqliConnection->prepare('
       SELECT * FROM trainers
       WHERE name = ?
       AND email = ?
       AND last_login > ?');

    $query->bind_param('first_param', 'second_param', $mail, time() - 3600);
    $query->execute();

P.S:

PDO wins this battle with ease. With support for twelve different database drivers and named parameters, we can ignore the small performance loss, and get used to its API. From a security standpoint, both of them are safe as long as the developer uses them the way they are supposed to be used

But while both PDO and MySQLi are quite fast, MySQLi performs insignificantly faster in benchmarks – ~2.5% for non-prepared statements, and ~6.5% for prepared ones.

And please test every query to your database - it's a better way to prevent injection.

editedJul 16 '14 at 2:27

Peter Mortensen

9,354 11 66 98

answered Oct 10 '12 at 14:53

imRcH

3,235 1 12 21

add a comment |

up vote 131 down vote

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 . "')");

answered Jun 12 '12 at 8:03

devOp

2,127 1 6 20

add a comment |

up vote 126 down vote

If you want to take advantage of cache engines, like Redis or Memcached, maybe DALMP could be a choice. It uses pureMySQLi. Check this:DALMP Database Abstraction Layer for MySQL using PHP.

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.DALMP Database Abstraction Layer for MySQL using PHP.

editedJul 16 '14 at 2:31

Peter Mortensen

9,354 11 66 98

answered Oct 15 '12 at 13:52

nbari

1,693 1 8 16

add a comment |

up vote 122 down vote

For those unsure of how to use PDO (coming from the mysql_ functions), I made avery, 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 formatyou 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));

answered Sep 19 '12 at 18:10

Xeoncross

18.9k 33 159 257

add a comment |

up vote 109 down vote

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

editedAug 4 '15 at 14:38

Ram Sharma

6,186 3 15 31

answered Aug 3 '12 at 19:59

Nicolas Finelli

1,314 1 6 6

add a comment |

up vote 106 down vote

A few guidelines for escaping special characters in SQL statements.

Don't use MySQL, this extension is deprecated, use MySQLi or PDO.

MySQLi

For manually escaping special characters in a string you can use the mysqli_real_escape_string function. The function will not work properly unless the correct character set is set withmysqli_set_charset.

Example:

$mysqli = new mysqli( 'host', 'user', 'password', 'database' );
$mysqli->set_charset( 'charset');

$string = $mysqli->real_escape_string( $string );
$mysqli->query( "INSERT INTO table (column) VALUES ('$string')" );

For automatic escaping of values with prepared statements, use mysqli_prepare, and mysqli_stmt_bind_param where types for the corresponding bind variables must be provided for an appropriate conversion:

Example:

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

$stmt->bind_param( "is", $integer, $string );

$stmt->execute();

No matter if you use prepared statements or mysqli_real_escape_string, you always have to know the type of input data you're working with.

So if you use a prepared statement, you must specify the types of the variables for mysqli_stmt_bind_param function.

And use of mysqli_real_escape_string is for, as the name says, escaping special characters in a string, so it will not make integers safe. The purpose of this function is to prevent breaking the strings in SQL statements, and the damage to the database that it could cause. mysqli_real_escape_string is a useful function when used properly, especially when combined with sprintf.

Example:

$string = "x' OR name LIKE '%John%";
$integer = '5 OR id != 0';

$query = sprintf( "SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string( $string ), $integer );

echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 5

$integer = '99999999999999999999';
$query = sprintf( "SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string( $string ), $integer );

echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 2147483647

editedJul 16 '14 at 2:47

community wiki

3 revs, 2 users 88%
Danijel

add a comment |

up vote 101 down vote

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

Use of mysql_real_escape_string(), which is a pre-defined function inPHP, 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.
The most advanced way is to use PDOs.

I hope this will help you.

Consider the following query:

$iId = mysql_real_escape_string("1 OR 1=1"); $sSql = "SELECT * FROM table WHERE id = $iId";

mysql_real_escape_string() will not protect here. If you use single quotes (' ') around your variables inside your query is what protects you against this. Here is an solution below for this:

$iId = (int) mysql_real_escape_string("1 OR 1=1"); $sSql = "SELECT * FROM table WHERE id = $iId";

This question has some good answers about this.

I suggest, using PDO is the best option.

Edit:

mysql_real_escape_string() is deprecated as of PHP 5.5.0. Use either mysqli or PDO.

An alternative to mysql_real_escape_string() is

string mysqli_real_escape_string ( mysqli $link , string $escapestr )

Example:

$iId = $mysqli->real_escape_string("1 OR 1=1");
$mysqli->query("SELECT * FROM table WHERE id = $iId");

editedApr 22 '15 at 12:13

answered Sep 14 '12 at 14:37

Soumalya Banerjee

1,422 1 11 22

add a comment |

up vote 92 down vote

Regarding to many useful answers, I hope to add some values to this thread. SQL injection is type of attack that can be done through user inputs (Inputs that filled by user and then used inside queries), The SQL injection patterns are correct query syntax while we can call it: bad queries for bad reasons, we assume that there might be bad person that try to get secret information (by passing access control) that affect the three principles of security (Confidentiality, Integrity, Availability).

Now, our point is to prevent security threats such as SQL injection attacks, the question asking (How to prevent SQL injection attack using PHP), be more realistic, data filtering or clearing input data is the case when using user-input data inside such query, using PHP or any other programming language is not the case, or as recommended by more people to use modern technology such as prepared statement or any other tools that currently supporting SQL injection prevention, consider that these tools not available anymore? how you secure your application?

My approach against SQL injection is: clearing user-input data before sending it to database (before using it inside any query).

Data filtering for (Converting unsafe data to safe data) Consider thatPDO and MySQLi not available, how can you secure your application? do you force me to use them? what about other languages other than PHP? I prefer to provide general ideas as it can be used for wider border not just for specific language.

SQL user (limiting user privilege): most common SQL operations are (SELECT, UPDATE, INSERT), then, why giving UPDATE privilege to a user that not require it? for example:login, and search pages are only using SELECT, then, why using db users in these pages with high privileges?RULE: do not create one database user for all privileges, for all SQL operations, you can create your scheme like (deluser, selectuser, updateuser) as usernames for easy usage.

see Principle of least privilege

Data filtering: before building any query user input should be validated and filtered, for programmers, it's important to define some properties for each user-input variables:data type, data pattern, and data length. a field that is a number between (x and y) must be exactly validated using exact rule, for a field that is a string (text): pattern is the case, for example: username must contain only some characters lets say [a-zA-Z0-9_-.] the length vary between (x and n) where x and n (integers, x <=n ).Rule: creating exact filters and validation rules are best practice for me.
Use other tools: Here, I will also agree with you that prepared statement (parametrized query) and Stored procedures, the disadvantages here is these ways requires advanced skills which are not exist in most users, the basic idea here is to distinguish between SQL query and the data that being used inside, both approach can be used even with unsafe data, because the user-input data here not add anything to the original query such as (any or x=x). for more information please readOWASP SQL Injection Prevention Cheat Sheet.

Now, if you are an advanced user, start using these defense as you like, but, for beginners, if they can't quickly implement stored procedure and prepared statement, it's better to filter input data as much they can.

Finally, let's consider that user sends this text below instead of entering his username:

[1] UNION SELECT IF(SUBSTRING(Password,1,1)='2',BENCHMARK(100000,SHA1(1)),0) User,Password FROM mysql.user WHERE User = 'root'

This input can be checked early without any prepared statement and stored procedures, but to be on safe side, using them starts after user-data filtering and validation.

Last point is detecting unexpected behavior which requires more effort and complexity, it's not recommended for normal web applications. Unexpected behavior in above user input is: SELECT, UNION, IF, SUBSTRING, BENCHMARK, SHA, root once these words detected, you can avoid the input.

UPDATE1:

A user commented that this post is useless, OK! Here is what OWASP.ORG provided:

Primary defenses:

Option #1: Use of Prepared Statements (Parameterized Queries)
Option #2: Use of Stored Procedures
Option #3: Escaping all User Supplied Input

Additional defenses:

Also Enforce: Least Privilege
Also Perform: White List Input Validation

As you may knew, claiming on any article should be supported by valid argument, at least one reference! Otherwise it's considered as attack and bad claim!

Update2:

From the PHP manual, PHP: Prepared Statements - Manual:

Escaping and SQL injection

Bound variables will be escaped automatically by the server. The server inserts their escaped values at the appropriate places into the statement template before execution. A hint must be provided to the server for the type of bound variable, to create an appropriate conversion. See the mysqli_stmt_bind_param() function for more information.

The automatic escaping of values within the server is sometimes considered a security feature to prevent SQL injection. The same degree of security can be achieved with non-prepared statements, if input values are escaped correctly.

Update3:

I created test cases for knowing how PDO and MySQLi sends the query to MySQL server when using prepared statement:

PDO:

$user = "''1''"; //Malicious keyword
$sql = 'SELECT * FROM awa_user WHERE userame =:username';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':username' => $user));

Query Log:

    189 Query SELECT * FROM awa_user WHERE userame ='\'\'1\'\''
    189 Quit

MySQLi:

$stmt = $mysqli->prepare("SELECT * FROM awa_user WHERE username =?")) {
$stmt->bind_param("s", $user);
$user = "''1''";
$stmt->execute();

Query Log:

    188 Prepare   SELECT * FROM awa_user WHERE username =?
    188 Execute   SELECT * FROM awa_user WHERE username ='\'\'1\'\''
    188 Quit

It's clear that a prepared statement is also escaping the data, nothing else.

As also mentioned in above statement The automatic escaping of values within the server is sometimes considered a security feature to prevent SQL injection. The same degree of security can be achieved with non-prepared statements, if input values are escaped correctly, therefore, this proves that data validation such as intval() is a good idea for integer values before sending any query, in addition, preventing malicious user data before sending the query iscorrect and valid approach.

Please see this question for more detail: PDO sends raw query to MySQL while Mysqli sends prepared query, both produce the same result

References:

editedMar 24 '14 at 21:05

community wiki

11 revs, 4 users 73%
user1646111

add a comment |

up vote 89 down vote

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 information about flush.

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

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

Learn more about GRANT.

editedJul 23 '14 at 9:17

answered Oct 25 '12 at 8:07

apurv nerlekar

1,236 10 17

add a comment |

up vote 77 down vote

A simple way would be to use a PHP framework like CodeIgniter or Laravel which have in-built features like filtering and active-record, so that you don't have to worry about these nuances.

editedJul 16 '14 at 2:48

community wiki

2 revs, 2 users 60%
Peter Mortensen

add a comment |

up vote 70 down vote

Warning : the approach described in this answer only applies for very specific scenarios and isn't secure since SQL injection attacks do not only rely on being able to injectX=Y.

If the attackers are trying to hack with the form via PHP's $_GET variable or with the URL's query string, you would be able to catch them if they're not secure.

RewriteCond %{QUERY_STRING} ([0-9]+)=([0-9]+)
RewriteRule ^(.*) ^/track.php

Because 1=1, 2=2, 1=2, 2=1,1+1=2, etc... are the common questions to an SQL database of an attacker. Maybe also it's used by many hacking applications.

But you must be careful, that you must not rewrite a safe query from your site. The code above is giving you a tip, to rewrite or redirect(it depends on you) that hacking-specific dynamic query string into a page that will store the attacker'sIP address, or EVEN THEIR COOKIES, history, browser, or any other sensitive information, so you can deal with them later by banning their account or contacting authorities.

editedJan 20 '15 at 7:33

community wiki

5 revs, 3 users 61%
Servant

add a comment |

up vote 64 down vote

There are so many answers for PHP and MySQL, but here is code forPHP and Oracle for preventing SQL injection as well as regular use of oci8 drivers:

$c = oci_connect($userName, $password, "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =$serverName)(PORT = 1521)))(CONNECT_DATA=(SID=$databaseName)))");
$strQuery = "UPDATE table SET field = :xx WHERE ID = 123"
$stmt = OCIParse($c, $strQuery);

OCIBindByName($stmt, ':xx', $fieldval);

$ok = OCIExecute($stmt);

editedJul 16 '14 at 2:53

community wiki

3 revs, 3 users 69%
Chintan Gor

add a comment |

up vote 59 down vote

Using PDO and MYSQLi is a good practice to prevent SQL injections, but if you really want to work with MySQL functions and queries, it would be better to use

mysql_real_escape_string

$unsafe_variable = mysql_real_escape_string($_POST['user_input']);

There are more ability to prevent this: like identify - if the input is a string, number, char or array, there are so many inbuilt functions to detect this. Also it would be better to use these functions to check input data.

is_string

$unsafe_variable = (is_string($_POST['user_input']) ? $_POST['user_input'] : '');

is_numeric

$unsafe_variable = (is_numeric($_POST['user_input']) ? $_POST['user_input'] : '');

And it is so much better to use those functions to check input data with mysql_real_escape_string.

editedJul 16 '14 at 2:51

community wiki

2 revs, 2 users 87%
Rakesh Sharma

5

Also, there is absolutely no point in checking $_POST array members with is_string() – Your Common Sense Jan 18 '14 at 7:06

9

WARNING!mysql_real_escape_string() is not infallible. – eggyal Apr 25 '14 at 14:54

1

@YourCommonSense you need to believe so what you want to update – Rakesh Sharma May 8 '14 at 8:38

3

mysql_real_escape_string is now deprecated, so its no longer a viable option. It will be removed from PHP in the future. Its best to move onto what the PHP or MySQL folks recommend. – jww Apr 8 '15 at 6:53

add a comment |

up vote 37 down vote

A good idea is to use an 'object-relational mapper' like Idiorm:

$user = ORM::for_table('user')
->where_equal('username', 'j4mie')
->find_one();

$user->first_name = 'Jamie';
$user->save();

$tweets = ORM::for_table('tweet')
    ->select('tweet.*')
    ->join('user', array(
        'user.id', '=', 'tweet.user_id'
    ))
    ->where_equal('user.username', 'j4mie')
    ->find_many();

foreach ($tweets as $tweet) {
    echo $tweet->text;
}

It not only saves you from SQL injections, but from syntax errors too!

editedMar 24 '14 at 20:56

community wiki

2 revs, 2 users 96%
Thomas Ahle

add a comment |

up vote 31 down vote

I've written this little function several years ago:

function sqlvprintf($query, $args)
{
    global $DB_LINK;
    $ctr = 0;
    ensureConnection(); // Connect to database if not connected already.
    $values = array();
    foreach ($args as $value)
    {
        if (is_string($value))
        {
            $value = "'" . mysqli_real_escape_string($DB_LINK, $value) . "'";
        }
        else if (is_null($value))
        {
            $value = 'NULL';
        }
        else if (!is_int($value) && !is_float($value))
        {
            die('Only numeric, string, array and NULL arguments allowed in a query. Argument '.($ctr+1).' is not a basic type, it\'s type is '. gettype($value). '.');
        }
        $values[] = $value;
        $ctr++;
    }
    $query = preg_replace_callback(
        '/{(\\d+)}/', 
        function($match) use ($values)
        {
            if (isset($values[$match[1]]))
            {
                return $values[$match[1]];
            }
            else
            {
                return $match[0];
            }
        },
        $query
    );
    return $query;
}

function runEscapedQuery($preparedQuery /*, ...*/)
{
    $params = array_slice(func_get_args(), 1);
    $results = runQuery(sqlvprintf($preparedQuery, $params)); // Run query and fetch results.   
    return $results;
}

This allows running statements in an one-liner C#-ish String.Format like:

runEscapedQuery("INSERT INTO Whatever (id, foo, bar) VALUES ({0}, {1}, {2})", $numericVar, $stringVar1, $stringVar2);

It escapes considering the variable type. If you try to parameterize table, column names, it would fail as it puts every string in quotes which is invalid syntax.

SECURITY UPDATE: The previous str_replace ve28 Answers 28

active oldest votes

up vote 5105 down vote accepted

+50

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:

Using PDO (for any supported database driver):

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute(array('name' => $name));

foreach ($stmt as $row) {
    // do something with $row
}

Using MySQLi (for MySQL):

$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
}

If you're connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (e.g.pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

Correctly setting up the connection

Note that when using PDO to access a MySQL database real prepared statements arenot 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 aFatal Error when something goes wrong. And it gives the developer the chance tocatch any error(s) which are thrown as PDOExceptions.

What is mandatory however is the first setAttribute() line, which tells PDO to disable emulated prepared statements and usereal prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to 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 callexecute, 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 an 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));

Can Prepared Statements Be Used For Dynamic Queries?

While you can still use prepared statements for the query parameters, the structure of the dynamic query itself cannot be parametrized and certain query features cannot be parametrized.

For these specific scenarios, the best thing to do is use a whitelist filter that restricts the possible values.

// Value whitelist
  // $dir can only be 'DESC' or 'ASC'
$dir = !empty($direction) ? 'DESC' : 'ASC';

editedSep 26 '15 at 5:54

Your Common Sense

100k 17 86 155

answered Sep 13 '08 at 12:30

Theo

87.8k 8 98 132

Just to add because I didn't see it anywhere else here, another line of defense is aweb application firewall (WAF) that can have rules be set to look for sql injection attacks: – jkerak Dec 29 '15 at 20:11

I woudl like to suggest use redbeanphp orm which is built on pdo. if you use pdo directly,it is really painful. – jewelhuq Jan 2 at 14:24

To learn more about 'PHP Data Objects' i.e. PDO visite these very good articals : 1.code.tutsplus.com/tutorials/… 2.code.tutsplus.com/tutorials/… – rakesh Jan 5 at 11:23

1

Also, the official documentation of mysql_query only allows to execute one query, so any other query besides ; is ignored. Even if this is already deprecated there are a lot of systems under PHP 5.5.0 and that may use this function. php.net/manual/en/function.mysql-query.php – Randall Valenciano Jan 19 at 17:40

2

I disagree with the practice of constantly wrapping every PDO statement in a try/catch. Fatal errors can be good. What's the point of just echo'ing out the errormessage and resuming an erroneous script, when not catching the exception gives you a nice stack trace? – Hugo Zink Feb 11 at 8:03

| show 10 more comments

up vote 958 down vote

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.

Warning:

As of PHP 5.5.0 mysql_real_escape_string and the mysql extension aredeprecated. Please use mysqli extension and mysqli::escape_string function instead

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

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 bymysql_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 byPolynomial 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.

editedMar 31 '14 at 21:44

Denys Vitali

356 3 16

answered Sep 13 '08 at 9:48

Matt Sheppard

46.3k 37 92 110

add a comment |

up vote 487 down vote

I'd recommend using PDO (PHP Data Objects) to run parameterized SQL queries.

Not only does this protect against SQL injection, it also speeds up queries.

And by using PDO rather than mysql_, mysqli_, and pgsql_ functions, you make your app a little more abstracted from the database, in the rare occurrence that you have to switch database providers.

editedMar 7 '14 at 6:12

Amarnath Balasubramanian

3,822 5 16 40

answered Sep 13 '08 at 0:02

Kibbee

43.1k 24 116 159

add a comment |

up vote 471 down vote

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 white-listing seems the only approach in this case.

Update

Although there is a general agreement on the best practices regarding SQL injection protection, there arestill many bad practices as well. And some of them too deeply rooted in the minds of PHP users. For instance, on this very page there are (although invisible to most visitors)more than 80 deleted answers - all removed by the community due to bad quality or promoting bad and outdated practices. Worse yet, some of bad answers aren't deleted but rather prospering.

For example, there are still(1) many(2) answers(3)suggesting(4)you(5) manual string escaping - an outdated approach that is proven to be insecure.

Or there is a slightly better answer that suggests just another method of string formatting and even boasts it as ultimate panacea. While of course it is not. This method is no better than regular string formatting yet it keeps all its drawbacks: it is applicable to strings only and, as any other manual formatting, it's essentially optional, not obligatory measure, prone to human error of any sort.

I think that all this because of one very old superstition, supported by such authorities likeOWASP or PHP manual, which proclaims equality between whatever "escaping" and protection from SQL injections.

Despite of what PHP manual said for ages, *_escape_string by no means makes data safe, and never has been intended to. Beside of being useless for any SQL part other than string, manual escaping is wrong because it is manual as opposite to automated.

And OWASP makes it even worse, stressing on escaping user input which is an utter nonsense: there should be no such words in the context of injection protection. Every variable is potentially dangerous - no matter of the source! Or, in other words - every variable have to be properly formatted to be put into query - no matter of the source again. It's destination that matters. The moment a developer starts to separate the sheep from the goats (thinking whether some particular variable "safe" or not) he does his first step to disaster. Not to mention that even the wording suggests bulk escaping at the entry point, resembling the very magic quotes feature - already despised, deprecated and removed.

Which is leaving us no choice other than prepared statements.

If you're still not convinced, here is a step-by step explanation I wrote, The Hitchhiker's Guide to SQL Injection protection, where I explained all these matters in detail, and even compiled a section entirely dedicated to bad practices and their disclosure.

editedJan 2 at 13:09

answered Nov 24 '11 at 9:50

Your Common Sense

100k 17 86 155

Great, well thought out article. I might add that using the Sanitize filters of PHP is kind of (but not exactly) a white listing of sorts. For example,FILTER_SANITIZE_NUMBER_INT only allows number characters, thereby white listing characters, not entire strings. In combination with prepared statements, it makes a good "belt and suspenders" approach. – Sablefoste Jan 20 at 17:12

1

@Sablefoste you don't need whitelisting here. Any sanitization will be redundant. Less rules to follow, the less mistakes you will make. Although you could do any validations, do it for sake of your application logic, but not for database. – Your Common Sense Jan 20 at 17:54

add a comment |

up vote 332 down vote

Use PDO and prepared queries.

($conn is a PDO object)

$stmt = $conn->prepare("INSERT INTO tbl VALUES(:id, :name)");
$stmt->bindValue(':id', $id);
$stmt->bindValue(':name', $name);
$stmt->execute();

editedSep 11 '15 at 17:02

casillas

3,892 2 25 44

answered Sep 13 '08 at 13:20

Imran

27.7k 15 68 104

add a comment |

up vote 305 down vote

As you can see, people suggest you to use prepared statements at the most. It's not wrong, but when your query is executedjust once per process, there would be a slightly performance penalty.

I was facing this issue, but I think I solved it in very sophisticated way - the way hackers use to avoid using quotes. I used this in conjuction with emulated prepared statements. I use it to preventall kinds of possible SQL injection attacks.

My approach:

If you expect input to be integer make sure it's really integer. In a variable-type language like PHP it is thisvery important. You can use for example this very simple but powerful solution:sprintf("SELECT 1,2,3 FROM table WHERE 4 = %u", $input);
If you expect anything else from integer hex it. If you hex it, you will perfectly escape all input. In C/C++ there's a function calledmysql_hex_string(), in PHP you can use bin2hex().

Don't worry about that the escaped string will have 2x size of its original length because even if you usemysql_real_escape_string, PHP has to allocate same capacity ((2*input_length)+1), which is the same.
This hex method is often used when you transfer binary data, but I see no reason why not use it on all data to prevent SQL injection attacks. Note that you have to prepend data with0x or use the MySQL function UNHEX instead.

So for example the query:

SELECT password FROM users WHERE name = 'root'

Will become:

SELECT password FROM users WHERE name = 0x726f6f74

or

SELECT password FROM users WHERE name = UNHEX('726f6f74')

Hex is the perfect escape. No way to inject.

Difference between UNHEX function and 0x prefix

There was some discussion in comments, so I finally want to make it clear. These two approaches are very similar, but they are a little different in some ways:

0x prefix can only be used on data columns such as char, varchar, text, block, binary, etc.
Also its use is a little complicated if you are about to insert an empty string. You'll have to entirely replace it with'', or you'll get an error.

UNHEX() works on any column; you do not have to worry about the empty string.

Hex methods are often used as attacks

Note that this hex method is often used as an SQL injection attack where integers are just like strings and escaped just withmysql_real_escape_string. Then you can avoid use of quotes.

For example, if you just do something like this:

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

an attack can inject you very easily. Consider the following injected code returned from your script:

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

and now just extract table structure:

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

And then just select whatever data ones want. Cool isn't it?

But if the coder of injectable site would hex it, no injection would be possible because the query would look like this:SELECT ... WHERE id = UNHEX('2d312075...3635')

editedJul 16 '14 at 2:19

Peter Mortensen

9,354 11 66 98

answered Oct 3 '12 at 14:07

Zaffy

7,640 4 24 56

2

@SumitGupta Yea, you did. MySQL doesnt concatenate with+ but with CONCAT. And to the performance: I dont think it affects performance because mysql has to parse data and it doesnt matter if origin is string or hex – Zaffy Jun 1 '13 at 23:49

2

@YourCommonSense What errors do you encounter? Be specific. – Zaffy Jul 1 '13 at 13:52

5

@YourCommonSense You dont understand the concept... If you want to have string in mysql you quote it like this'root' or you can hex it 0x726f6f74 BUT if you want a number and send it as string you will probably write '42' not CHAR(42) ... '42' in hex would be0x3432 not 0x42 – Zaffy Jul 1 '13 at 14:07

5

@YourCommonSense I have nothing to say... just lol... if you still want to try hex on numeric fields, see second comment. I bet with you that it'll work. – Zaffy Jul 1 '13 at 14:24

3

@YourCommonSense you still dont understand ? You cannot use 0x and concat because if the string is empty you will end with an error. If you want simple alternative to your query try this one SELECT title FROM article WHERE id = UNHEX(' . bin2hex($_GET["id"]) . ') – Zaffy Aug 1 '13 at 12:33

| show 18 more comments

up vote 271 down vote

IMPORTANT

The best way to prevent SQL Injection is to use Prepared Statementsinstead of escaping, as the accepted answer demonstrates.

There are libraries such as Aura.Sql and EasyDB that allow developers to use prepared statements easier. To learn more about why prepared statements are better atstopping SQL injection, refer to this mysql_real_escape_string() bypass and recently fixed Unicode SQL Injection vulnerabilities in WordPress.

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.

mysql_real_escape_string takes 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 details in MySQL - SQL Injection Prevention.

editedJun 1 '15 at 13:45

Scott Arciszewski

8,793 3 24 71

answered Jun 17 '11 at 4:00

rahularyansharma

6,481 8 44 97

18

This is the best you can do with legacy mysql extension. For new code, you're advised to switch to mysqli or PDO. – Álvaro González Feb 26 '13 at 12:42

4

I am not agree with this 'a specially-made function to prevent these attacks'. I think thatmysql_real_escape_string purpose is in allow to build correct SQL query for every input data-string. Prevention sql-injection is the side-effect of this function. – sectus Jul 9 '13 at 5:01

1

you dont use functions to write correct input data-strings. You just write correct ones that don't need escaping or have already been escaped. mysql_real_escape_string() may have been designed with the purpose you mention in mind, but its only value is preventing injection. – Nazca Mar 12 '14 at 22:38

7

WARNING!mysql_real_escape_string() is not infallible. – eggyal Apr 25 '14 at 14:50

3

mysql_real_escape_string is now deprecated, so its no longer a viable option. It will be removed in the future from PHP. Its best to move onto what the PHP or MySQL folks recommend. – jww Apr 8 '15 at 6:41

| show 3 more comments

up vote 220 down vote

Security Warning: This answer is not in line with security best practices.Escaping is inadequate to prevent SQL injection, use prepared statements instead. Use the strategy outlined below at your own risk. (Also,mysql_real_escape_string() was removed in PHP 7.)

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

editedJan 2 at 3:47

Scott Arciszewski

8,793 3 24 71

answered Sep 13 '08 at 0:15

Tanerax

3,879 4 20 24

13

I have tried your example and it's work fine for me.Could you clear "this won't solve every problem" – Chinook Apr 22 '12 at 20:31

6

If you don't quote the string, it's still injectable. Take$q = "SELECT col FROM tbl WHERE x = $safe_var"; for example. Setting$safe_var to 1 UNION SELECT password FROM users works in this case because of the lack of quotes. It's also possible to inject strings into the query usingCONCAT and CHR. – Polynomial Apr 16 '13 at 18:06

5

@glglgl That's fine, if you're willing to accept the risk of someone forgetting a quote somewhere in your entire application. Bad development practices keep me in a job ;) – Polynomial Jul 10 '13 at 9:05

6

WARNING!mysql_real_escape_string() is not infallible. – eggyal Apr 25 '14 at 14:46

1

mysql_real_escape_string is now deprecated, so its no longer a viable option. It will be removed in the future from PHP. Its best to move onto what the PHP or MySQL folks recommend. – jww Apr 8 '15 at 6:37

| show 4 more comments

up vote 207 down vote

Whatever you do end up using, make sure that you check your input hasn't already been mangled bymagic_quotes or some other well-meaning rubbish, and if necessary, run it throughstripslashes or whatever to sanitise it.

answered Dec 8 '08 at 5:26

Rob

37.1k 2 53 84

7

Indeed; running with magic_quotes switched on just encourages poor practice. However, sometimes you can't always control the environment to that level - either you don't have access to manage the server, or your application has to coexist with applications that (shudder) depend on such configuration. For these reasons, it's good to write portable applications - though obviously the effort is wasted if you do control the deployment environment, e.g. because it's an in-house application, or only going to be used in your specific environment. – Rob Apr 24 '11 at 17:04

13

As of PHP 5.4, the abomination known as 'magic quotes' has beenkilled dead. And good riddance to bad rubbish. – BryanH Jan 16 '13 at 22:45

add a comment |

up vote 202 down vote

Parameterized query AND input validation is the way to go. There is many scenarios under which SQL injection may occur, even thoughmysql_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 cases, you can't use ' to protect the encapsulation.

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

editedMay 30 '14 at 18:33

Peter Mortensen

9,354 11 66 98

answered Jul 3 '11 at 21:50

Cedric

2,301 1 11 11

Dont take user inputs where not neccessary. sanitize and filter user inputs before using it for database querying. Use the whitelist approach it easier to create white list rather than blacklist read about it here. schneier.com/blog/archives/2011/01/whitelisting_vs.html – Danyal Fayyaz Sep 2 '15 at 11:58

You can prevent SQL injection if you adopt an input validation technique in which user input is authenticated against a set of defined rules for length, type and syntax and also against business rules. – Josip Ivic Sep 15 '15 at 8:07

add a comment |

up vote 167 down vote

In my opinion, the best way to generally prevent SQL injection in your PHP application (or any web application, 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 application in a sensible way and make you think more about loading and saving objects than about securely constructing single SQL queries.

editedJul 16 '14 at 2:05

Peter Mortensen

9,354 11 66 98

answered Jul 3 '12 at 10:14

Johannes Fahrenkrug

20.1k 7 67 103

add a comment |

up vote 155 down vote

There are many ways of preventing SQL injections and other SQL hacks. You can easily find it on the Internet (Google Search). Of coursePDO 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.

editedSep 17 '12 at 22:12

Peter Mortensen

9,354 11 66 98

answered Jul 23 '12 at 10:19

Manish Shrivastava

9,132 5 54 75

add a comment |

up vote 151 down vote

I favor stored procedures (MySQL has had stored procedures support since 5.0) from a security point of view - the advantages are -

Most databases (including MySQL) enable user access to be restricted to executing stored procedures. The fine grained security access control is useful to prevent escalation of privileges attacks. This prevents compromised applications from being able to run SQL directly against the database.
They abstract the raw SQL query from the application so less information of the database structure is available to the application. This makes it harder for people to understand the underlying structure of the database and design suitable attacks.
They accept only parameters, so the advantages of parameterized queries are there. Of course - IMO you still need to sanitize your input - especially if you are using dynamic SQL inside the stored procedure.

The disadvantages are -

They (stored procedures) are tough to maintain and tend to multiply very quickly. This makes managing them an issue.
They are not very suitable for dynamic queries - if they are built to accept dynamic code as parameters then a lot of the advantages are negated.

editedMay 30 '14 at 18:29

Peter Mortensen

9,354 11 66 98

answered Nov 3 '09 at 18:05

Nikhil

2,787 2 14 25

add a comment |

up vote 148 down vote

I think if someone wants to use PHP and MySQL or some other dataBase server:

Think about learning PDO (PHP Data Objects) – it is a database access layer providing a uniform method of access to multiple databases.
Think about learning MySQLi
Use native PHP functions like: strip_tags, mysql_real_escape_string or if variable numeric, just (int)$foo. Read more about type of variables in PHPhere. If you're using libraries such as PDO or MySQLi, always usePDO::quote() andmysqli_real_escape_string().

Libraries examples:

---- PDO

----- No placeholders - ripe for SQL injection! It's bad

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values ($name, $addr, $city)");

----- Unnamed placeholders

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values (?, ?, ?);

----- Named placeholders

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) value (:name, :addr, :city)");

--- MySQLi

$request = $mysqliConnection->prepare('
       SELECT * FROM trainers
       WHERE name = ?
       AND email = ?
       AND last_login > ?');

    $query->bind_param('first_param', 'second_param', $mail, time() - 3600);
    $query->execute();

P.S:

PDO wins this battle with ease. With support for twelve different database drivers and named parameters, we can ignore the small performance loss, and get used to its API. From a security standpoint, both of them are safe as long as the developer uses them the way they are supposed to be used

But while both PDO and MySQLi are quite fast, MySQLi performs insignificantly faster in benchmarks – ~2.5% for non-prepared statements, and ~6.5% for prepared ones.

And please test every query to your database - it's a better way to prevent injection.

editedJul 16 '14 at 2:27

Peter Mortensen

9,354 11 66 98

answered Oct 10 '12 at 14:53

imRcH

3,235 1 12 21

add a comment |

up vote 131 down vote

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 . "')");

answered Jun 12 '12 at 8:03

devOp

2,127 1 6 20

add a comment |

up vote 126 down vote

If you want to take advantage of cache engines, like Redis or Memcached, maybe DALMP could be a choice. It uses pureMySQLi. Check this:DALMP Database Abstraction Layer for MySQL using PHP.

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.DALMP Database Abstraction Layer for MySQL using PHP.

editedJul 16 '14 at 2:31

Peter Mortensen

9,354 11 66 98

answered Oct 15 '12 at 13:52

nbari

1,693 1 8 16

add a comment |

up vote 122 down vote

For those unsure of how to use PDO (coming from the mysql_ functions), I made avery, 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 formatyou 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));

answered Sep 19 '12 at 18:10

Xeoncross

18.9k 33 159 257

add a comment |

up vote 109 down vote

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

editedAug 4 '15 at 14:38

Ram Sharma

6,186 3 15 31

answered Aug 3 '12 at 19:59

Nicolas Finelli

1,314 1 6 6

add a comment |

up vote 106 down vote

A few guidelines for escaping special characters in SQL statements.

Don't use MySQL, this extension is deprecated, use MySQLi or PDO.

MySQLi

For manually escaping special characters in a string you can use the mysqli_real_escape_string function. The function will not work properly unless the correct character set is set withmysqli_set_charset.

Example:

$mysqli = new mysqli( 'host', 'user', 'password', 'database' );
$mysqli->set_charset( 'charset');

$string = $mysqli->real_escape_string( $string );
$mysqli->query( "INSERT INTO table (column) VALUES ('$string')" );

For automatic escaping of values with prepared statements, use mysqli_prepare, and mysqli_stmt_bind_param where types for the corresponding bind variables must be provided for an appropriate conversion:

Example:

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

$stmt->bind_param( "is", $integer, $string );

$stmt->execute();

No matter if you use prepared statements or mysqli_real_escape_string, you always have to know the type of input data you're working with.

So if you use a prepared statement, you must specify the types of the variables for mysqli_stmt_bind_param function.

And use of mysqli_real_escape_string is for, as the name says, escaping special characters in a string, so it will not make integers safe. The purpose of this function is to prevent breaking the strings in SQL statements, and the damage to the database that it could cause. mysqli_real_escape_string is a useful function when used properly, especially when combined with sprintf.

Example:

$string = "x' OR name LIKE '%John%";
$integer = '5 OR id != 0';

$query = sprintf( "SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string( $string ), $integer );

echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 5

$integer = '99999999999999999999';
$query = sprintf( "SELECT id, email, pass, name FROM members WHERE email ='%s' AND id = %d", $mysqli->real_escape_string( $string ), $integer );

echo $query;
// SELECT id, email, pass, name FROM members WHERE email ='x\' OR name LIKE \'%John%' AND id = 2147483647

editedJul 16 '14 at 2:47

community wiki

3 revs, 2 users 88%
Danijel

add a comment |

up vote 101 down vote

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

Use of mysql_real_escape_string(), which is a pre-defined function inPHP, 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.
The most advanced way is to use PDOs.

I hope this will help you.

Consider the following query:

$iId = mysql_real_escape_string("1 OR 1=1"); $sSql = "SELECT * FROM table WHERE id = $iId";

mysql_real_escape_string() will not protect here. If you use single quotes (' ') around your variables inside your query is what protects you against this. Here is an solution below for this:

$iId = (int) mysql_real_escape_string("1 OR 1=1"); $sSql = "SELECT * FROM table WHERE id = $iId";

This question has some good answers about this.

I suggest, using PDO is the best option.

Edit:

mysql_real_escape_string() is deprecated as of PHP 5.5.0. Use either mysqli or PDO.

An alternative to mysql_real_escape_string() is

string mysqli_real_escape_string ( mysqli $link , string $escapestr )

Example:

$iId = $mysqli->real_escape_string("1 OR 1=1");
$mysqli->query("SELECT * FROM table WHERE id = $iId");

editedApr 22 '15 at 12:13

answered Sep 14 '12 at 14:37

Soumalya Banerjee

1,422 1 11 22

add a comment |

up vote 92 down vote

Regarding to many useful answers, I hope to add some values to this thread. SQL injection is type of attack that can be done through user inputs (Inputs that filled by user and then used inside queries), The SQL injection patterns are correct query syntax while we can call it: bad queries for bad reasons, we assume that there might be bad person that try to get secret information (by passing access control) that affect the three principles of security (Confidentiality, Integrity, Availability).

Now, our point is to prevent security threats such as SQL injection attacks, the question asking (How to prevent SQL injection attack using PHP), be more realistic, data filtering or clearing input data is the case when using user-input data inside such query, using PHP or any other programming language is not the case, or as recommended by more people to use modern technology such as prepared statement or any other tools that currently supporting SQL injection prevention, consider that these tools not available anymore? how you secure your application?

My approach against SQL injection is: clearing user-input data before sending it to database (before using it inside any query).

Data filtering for (Converting unsafe data to safe data) Consider thatPDO and MySQLi not available, how can you secure your application? do you force me to use them? what about other languages other than PHP? I prefer to provide general ideas as it can be used for wider border not just for specific language.

SQL user (limiting user privilege): most common SQL operations are (SELECT, UPDATE, INSERT), then, why giving UPDATE privilege to a user that not require it? for example:login, and search pages are only using SELECT, then, why using db users in these pages with high privileges?RULE: do not create one database user for all privileges, for all SQL operations, you can create your scheme like (deluser, selectuser, updateuser) as usernames for easy usage.

see Principle of least privilege

Data filtering: before building any query user input should be validated and filtered, for programmers, it's important to define some properties for each user-input variables:data type, data pattern, and data length. a field that is a number between (x and y) must be exactly validated using exact rule, for a field that is a string (text): pattern is the case, for example: username must contain only some characters lets say [a-zA-Z0-9_-.] the length vary between (x and n) where x and n (integers, x <=n ).Rule: creating exact filters and validation rules are best practice for me.
Use other tools: Here, I will also agree with you that prepared statement (parametrized query) and Stored procedures, the disadvantages here is these ways requires advanced skills which are not exist in most users, the basic idea here is to distinguish between SQL query and the data that being used inside, both approach can be used even with unsafe data, because the user-input data here not add anything to the original query such as (any or x=x). for more information please readOWASP SQL Injection Prevention Cheat Sheet.

Now, if you are an advanced user, start using these defense as you like, but, for beginners, if they can't quickly implement stored procedure and prepared statement, it's better to filter input data as much they can.

Finally, let's consider that user sends this text below instead of entering his username:

[1] UNION SELECT IF(SUBSTRING(Password,1,1)='2',BENCHMARK(100000,SHA1(1)),0) User,Password FROM mysql.user WHERE User = 'root'

This input can be checked early without any prepared statement and stored procedures, but to be on safe side, using them starts after user-data filtering and validation.

Last point is detecting unexpected behavior which requires more effort and complexity, it's not recommended for normal web applications. Unexpected behavior in above user input is: SELECT, UNION, IF, SUBSTRING, BENCHMARK, SHA, root once these words detected, you can avoid the input.

UPDATE1:

A user commented that this post is useless, OK! Here is what OWASP.ORG provided:

Primary defenses:

Option #1: Use of Prepared Statements (Parameterized Queries)
Option #2: Use of Stored Procedures
Option #3: Escaping all User Supplied Input

Additional defenses:

Also Enforce: Least Privilege
Also Perform: White List Input Validation

As you may knew, claiming on any article should be supported by valid argument, at least one reference! Otherwise it's considered as attack and bad claim!

Update2:

From the PHP manual, PHP: Prepared Statements - Manual:

Escaping and SQL injection

Bound variables will be escaped automatically by the server. The server inserts their escaped values at the appropriate places into the statement template before execution. A hint must be provided to the server for the type of bound variable, to create an appropriate conversion. See the mysqli_stmt_bind_param() function for more information.

The automatic escaping of values within the server is sometimes considered a security feature to prevent SQL injection. The same degree of security can be achieved with non-prepared statements, if input values are escaped correctly.

Update3:

I created test cases for knowing how PDO and MySQLi sends the query to MySQL server when using prepared statement:

PDO:

$user = "''1''"; //Malicious keyword
$sql = 'SELECT * FROM awa_user WHERE userame =:username';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':username' => $user));

Query Log:

    189 Query SELECT * FROM awa_user WHERE userame ='\'\'1\'\''
    189 Quit

MySQLi:

$stmt = $mysqli->prepare("SELECT * FROM awa_user WHERE username =?")) {
$stmt->bind_param("s", $user);
$user = "''1''";
$stmt->execute();

Query Log:

    188 Prepare   SELECT * FROM awa_user WHERE username =?
    188 Execute   SELECT * FROM awa_user WHERE username ='\'\'1\'\''
    188 Quit

It's clear that a prepared statement is also escaping the data, nothing else.

As also mentioned in above statement The automatic escaping of values within the server is sometimes considered a security feature to prevent SQL injection. The same degree of security can be achieved with non-prepared statements, if input values are escaped correctly, therefore, this proves that data validation such as intval() is a good idea for integer values before sending any query, in addition, preventing malicious user data before sending the query iscorrect and valid approach.

Please see this question for more detail: PDO sends raw query to MySQL while Mysqli sends prepared query, both produce the same result

References:

editedMar 24 '14 at 21:05

community wiki

11 revs, 4 users 73%
user1646111

add a comment |

up vote 89 down vote

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 information about flush.

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

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

Learn more about GRANT.

editedJul 23 '14 at 9:17

answered Oct 25 '12 at 8:07

apurv nerlekar

1,236 10 17

add a comment |

up vote 77 down vote

A simple way would be to use a PHP framework like CodeIgniter or Laravel which have in-built features like filtering and active-record, so that you don't have to worry about these nuances.

editedJul 16 '14 at 2:48

community wiki

2 revs, 2 users 60%
Peter Mortensen

add a comment |

up vote 70 down vote

Warning : the approach described in this answer only applies for very specific scenarios and isn't secure since SQL injection attacks do not only rely on being able to injectX=Y.

If the attackers are trying to hack with the form via PHP's $_GET variable or with the URL's query string, you would be able to catch them if they're not secure.

RewriteCond %{QUERY_STRING} ([0-9]+)=([0-9]+)
RewriteRule ^(.*) ^/track.php

Because 1=1, 2=2, 1=2, 2=1,1+1=2, etc... are the common questions to an SQL database of an attacker. Maybe also it's used by many hacking applications.

But you must be careful, that you must not rewrite a safe query from your site. The code above is giving you a tip, to rewrite or redirect(it depends on you) that hacking-specific dynamic query string into a page that will store the attacker'sIP address, or EVEN THEIR COOKIES, history, browser, or any other sensitive information, so you can deal with them later by banning their account or contacting authorities.

editedJan 20 '15 at 7:33

community wiki

5 revs, 3 users 61%
Servant

add a comment |

up vote 64 down vote

There are so many answers for PHP and MySQL, but here is code forPHP and Oracle for preventing SQL injection as well as regular use of oci8 drivers:

$c = oci_connect($userName, $password, "(DESCRIPTION=(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =$serverName)(PORT = 1521)))(CONNECT_DATA=(SID=$databaseName)))");
$strQuery = "UPDATE table SET field = :xx WHERE ID = 123"
$stmt = OCIParse($c, $strQuery);

OCIBindByName($stmt, ':xx', $fieldval);

$ok = OCIExecute($stmt);

editedJul 16 '14 at 2:53

community wiki

3 revs, 3 users 69%
Chintan Gor

add a comment |

up vote 59 down vote

Using PDO and MYSQLi is a good practice to prevent SQL injections, but if you really want to work with MySQL functions and queries, it would be better to use

mysql_real_escape_string

$unsafe_variable = mysql_real_escape_string($_POST['user_input']);

There are more ability to prevent this: like identify - if the input is a string, number, char or array, there are so many inbuilt functions to detect this. Also it would be better to use these functions to check input data.

is_string

$unsafe_variable = (is_string($_POST['user_input']) ? $_POST['user_input'] : '');

is_numeric

$unsafe_variable = (is_numeric($_POST['user_input']) ? $_POST['user_input'] : '');

And it is so much better to use those functions to check input data with mysql_real_escape_string.

editedJul 16 '14 at 2:51

community wiki

2 revs, 2 users 87%
Rakesh Sharma

5

Also, there is absolutely no point in checking $_POST array members with is_string() – Your Common Sense Jan 18 '14 at 7:06

9

WARNING!mysql_real_escape_string() is not infallible. – eggyal Apr 25 '14 at 14:54

1

@YourCommonSense you need to believe so what you want to update – Rakesh Sharma May 8 '14 at 8:38

3

mysql_real_escape_string is now deprecated, so its no longer a viable option. It will be removed from PHP in the future. Its best to move onto what the PHP or MySQL folks recommend. – jww Apr 8 '15 at 6:53

add a comment |

up vote 37 down vote

A good idea is to use an 'object-relational mapper' like Idiorm:

$user = ORM::for_table('user')
->where_equal('username', 'j4mie')
->find_one();

$user->first_name = 'Jamie';
$user->save();

$tweets = ORM::for_table('tweet')
    ->select('tweet.*')
    ->join('user', array(
        'user.id', '=', 'tweet.user_id'
    ))
    ->where_equal('user.username', 'j4mie')
    ->find_many();

foreach ($tweets as $tweet) {
    echo $tweet->text;
}

It not only saves you from SQL injections, but from syntax errors too!

editedMar 24 '14 at 20:56

community wiki

2 revs, 2 users 96%
Thomas Ahle

add a comment |

up vote 31 down vote

I've written this little function several years ago:

function sqlvprintf($query, $args)
{
    global $DB_LINK;
    $ctr = 0;
    ensureConnection(); // Connect to database if not connected already.
    $values = array();
    foreach ($args as $value)
    {
        if (is_string($value))
        {
            $value = "'" . mysqli_real_escape_string($DB_LINK, $value) . "'";
        }
        else if (is_null($value))
        {
            $value = 'NULL';
        }
        else if (!is_int($value) && !is_float($value))
        {
            die('Only numeric, string, array and NULL arguments allowed in a query. Argument '.($ctr+1).' is not a basic type, it\'s type is '. gettype($value). '.');
        }
        $values[] = $value;
        $ctr++;
    }
    $query = preg_replace_callback(
        '/{(\\d+)}/', 
        function($match) use ($values)
        {
            if (isset($values[$match[1]]))
            {
                return $values[$match[1]];
            }
            else
            {
                return $match[0];
            }
        },
        $query
    );
    return $query;
}

function runEscapedQuery($preparedQuery /*, ...*/)
{
    $params = array_slice(func_get_args(), 1);
    $results = runQuery(sqlvprintf($preparedQuery, $params)); // Run query and fetch results.   
    return $results;
}

This allows running statements in an one-liner C#-ish String.Format like:

runEscapedQuery("INSERT INTO Whatever (id, foo, bar) VALUES ({0}, {1}, {2})", $numericVar, $stringVar1, $stringVar2);

It escapes considering the variable type. If you try to parameterize table, column names, it would fail as it puts every string in quotes which is invalid syntax.

SECURITY UPDATE: The previous str_replace version allowed injections by adding {#} tokens into user data. Thispreg_replace_callback version doesn't cause problems if the replacement contains these tokens.

editedJun 29 '14 at 22:30

community wiki

3 revs
Calmarius

add a comment |

protected by AVD Jun 6 '12 at 9:59

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10reputation on this site.

Would you like to answer one of these unanswered questions instead?

rsion allowed injections by adding {#} tokens into user data. This preg_replace_callback version doesn't cause problems if the replacement contains these tokens.

editedJun 29 '14 at 22:30

community wiki

3 revs
Calmarius

add a comment |

How can I prevent SQL-injection in PHP?

Correctly setting up the connection

Explanation

Can Prepared Statements Be Used For Dynamic Queries?

Warning:

Update

My approach:

Difference between UNHEX function and 0x prefix

Hex methods are often used as attacks

UPDATE1:

Update2:

Update3:

It's clear that a prepared statement is also escaping the data, nothing else.

protected by AVD Jun 6 '12 at 9:59

Correctly setting up the connection

Explanation

Can Prepared Statements Be Used For Dynamic Queries?

Warning:

Update

My approach:

Difference between UNHEX function and 0x prefix

Hex methods are often used as attacks

UPDATE1:

Update2:

Update3:

It's clear that a prepared statement is also escaping the data, nothing else.

Correctly setting up the connection

Explanation

Can Prepared Statements Be Used For Dynamic Queries?

Warning:

Update

My approach:

Difference between UNHEX function and 0x prefix

Hex methods are often used as attacks

UPDATE1:

Update2:

Update3:

It's clear that a prepared statement is also escaping the data, nothing else.

protected by AVD Jun 6 '12 at 9:59

相关文章