This question's answers are a collaborative effort: if you see something that can be improved, just edit the answer to improve it! No additional answers can be added here

181

Note that that particular example will not work, because the mysql_ lib does not allow executing 2 queries in one statement. As long as you keep the manipulation in a single statement the injection will work.
–
JohanAug 19 '12 at 14:08

27

It also shouldn't work if you have given proper permissions to the user and assuming that the average sql user should not be granted permissions to drop tables it shouldn't work.
–
VBAssassinNov 29 '12 at 12:15

28 Answers
28

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.

Correctly setting up the connection

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

In the above example the error mode isn't strictly necessary, but it is advised to add it. This way the script will not stop with a Fatal Error when something goes wrong. And it gives the developer the chance to catch 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 use real 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).

Explanation

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

The important thing here is that the parameter values are combined with the compiled statement, not 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):

This make sense, However, PDO is an extension right? meaning it needs to be installed? Is there a way I can check to see if it is installed? Also I am using a shared hosting, so if it is not installed and my hosting provider cannot/will not install it, is there an alternative to using a PDO? Thank You!!
–
JD IsaacksJan 5 '10 at 21:31

166

php.net/manual/en/pdo.installation.php PDO is bundled by default since PHP 5.1. Not all drivers for all databases may be installed, but if your host supports MySQL and PHP later than 5.1 it would be very surprising if it didn't have the MySQL PDO driver installed. Create a page with <?php phpinfo(); ?> and view it in a browser, look for PDO and you will see info on which drivers are installed.
–
TheoJan 6 '10 at 12:29

71

The protection comes from using bound parameters, not from using prepared statement (it is just that people tend to switch to using prepared statements at the same time as bound parameters, so the two ideas get conflated).
–
QuentinOct 3 '11 at 10:53

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.

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

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

If you want to alter the structure of the SQL based on user input, parameterised queries are not going to help, and the escaping required is not covered by mysql_real_escape_string. In this kind of case you would be better off passing the user's input through a whitelist to ensure only 'safe' values are allowed through.

If you use integers from user input in a condition and take the mysql_real_escape_string approach, you will suffer from the problem described by Polynomial in the comments below. This case is trickier because integers would not be surrounded by quotes, so you could deal with by validating that the user input contains only digits.

I like this much better than the accepted answer! But is mysql_real_escape_string really as safe as parameterization?
–
CawasApr 29 '11 at 14:57

39

Something is very wrong in PHP land if mysql_real_escape_string doesn't appropriately escape all special characters. That said, it's easier to look at code using parameterization and know that it's correct than code using escaping functions.
–
Matt SheppardMay 2 '11 at 0:33

210

-1 because concatenation-style query building is always a bad idea. The mysql_real_escape_string function is not a catch-all. It only escapes special characters, so SELECT * FROM users WHERE score = $var is still vulnerable to $var = "1 OR 1 = 1".
–
PolynomialDec 5 '11 at 12:25

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

@MichaelMior php.net/manual/en/pdo.prepare.php "Calling PDO::prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information" - Although I admit there's some knowledge bleed-through happening with other Dbs as I'm not sure you can do it cross-request in PHP. Mysql is able to cache: dev.mysql.com/doc/refman/5.6/en/statement-caching.html
–
BasicMar 24 '14 at 8:06

3

@Justinᚅᚔᚈᚄᚒᚔ Prepared statements can make use of the query cache as of MySQL 5.1.17. But prepared statements themselves were not cached until 5.6 as noted in another comment.
–
Michael MiorMar 24 '14 at 14:04

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 asAs long as you are adding dynamic parts to the query using placeholders (and these placeholders properly processed of course), you can be sure that your query is safe.

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

What we could really do with is a clean, simple library to implement this - it's certainly not in PDO, and I really don't want the wasteful overhead of prepared queries just to get half-baked parameter binding.
–
SynchroDec 5 '12 at 10:59

9

@Synchro Finally I am done with it, and I have to say that I'm proud of my work, as I've got it easy, fast and lightweight. You can find the link in my profile.
–
Your Common SenseFeb 15 '13 at 18:11

7

Your example above would immediately generate problem, is SORT flag is not found in the orders. YOu would end up with query "Select * from 'table' order by"; and in mysql case that would render error: "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1" so, make proper examples not to confuse the developers.
–
janchaAug 21 '13 at 8:54

7

also, as array_search suggests, $key would be false, if not found, so relaying on $orders[false] is very nasty and should be discouraged.
–
janchaAug 21 '13 at 8:56

4

Your two comments contradicts with each other :)
–
Your Common SenseFeb 16 '14 at 17:12

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

I was facing this issue, but I think I solved it 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 prevent all 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 this very 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 called mysql_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 use mysql_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 with 0x 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 with mysql_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:

@Zaffy got to admit, the two proposed solutions are elegant beyond any solution I've seen to date. You sir and your answer deserve much more recognition!
–
KhezMar 11 '13 at 12:57

3

@YourCommonSense You are wrong! Number 42 are two bytes 4 and 2 so the result will be 0x3432. Also be aware of using it like that because if id doesnt contain anything you will get id = 0x and end up with an error.
–
ZaffyMar 19 '13 at 8:49

3

@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 be 0x3432 not 0x42
–
ZaffyJul 1 '13 at 14:07

14

A note to readers who still can't get the point: This answer contradicts with itself. It cannot solve the very problem query that posted as a bad example. This answer is wrong and deceiving. The approach is even worse than regular manual formatting, not to mention prepared statements.
–
Your Common SenseJul 1 '13 at 14:18

5

Excellent method, HEXing is the most reliable escaping method of all that are discussed on this page, also simple and fast, and it is perfectly sufficient to solve SQL injection. "Your Common Sense" is doing great disservice to the public by slandering it. Of course, prepared statements are a different solution and conceptually alter the interaction with database.
–
MKaamaApr 23 '14 at 18:15

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 \'.

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

2

I am not agree with this 'a specially-made function to prevent these attacks'. I think that mysql_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.
–
sectusJul 9 '13 at 5:01

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.
–
NazcaMar 12 '14 at 22:38

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

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.
–
RobApr 24 '11 at 17:04

9

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

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

5

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 using CONCAT and CHR.
–
PolynomialApr 16 '13 at 18:06

@Polynomial Completely right, but I'd see this merely as wrong usage. As long as you use it correctly, it will definitely work.
–
glglglJul 10 '13 at 7:30

-1. Input validation has absolutely nothing to do with SQL. This is one of many delusions connected to the problem. You cannot validate input by the time when SQL query have to be executed. So, you just cannot tell which input data have to be formatted and which way. That's just completely different realms. Not to mention that input validation rules may change, to reflect business logic change... and thus leave SQL open. One have to format their SQL always, despite of any business or validation logic
–
Your Common SenseFeb 12 '14 at 17:02

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.

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

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.

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.

I have noticed a lot of down-votes to this answer but no comments or any reasons as to why. I would appreciate the courtesy of letting me know why you think this answer deserves a down-vote so i have an opportunity to respond.
–
NikhilMar 2 '11 at 9:16

18

I haven't downvoted, but stored procedures are generally frowned upon, because you put business logic, which belongs into your PHP scripts, into the database, making maintainance a nightmare.
–
NikiCApr 10 '11 at 8:58

3

@Nikhil: Stored procedures themselves do little to protect against SQL injection. Unless you use parametrized queries to run the stored procedure, an attacker can still inject malicious SQL into a query. The only real benefit that you've cited is that it hides the database structure, but that doesn't mean that attacks are very much more difficult. The rest of the benefits you claim are just so much nonsense.
–
greyfadeApr 20 '11 at 17:07

3

@nikic - Yes, using stored procedures may encourage people to put business logic in them when it is not appropriate. But IMO that is a code smell which should be caught in your code review.
–
NikhilApr 25 '11 at 5:54

3

@Nikhil: Then I apologize for not being clearer: Stored procedures are still vulnerable to the same kinds of injection by a knowledgable attacker that a plain query is. Types be damned if you're concatenating strings to make the query. The only sure protection is a parametrized call to the SP (by which it is technically infeasible to inject SQL), negating the whole argument completely. I contend that it is still possible for an attacker to pass dummy arguments to the SP if you don't use a parametrized call, and in doing so gain an injection vector. SPs themselves do not protect you.
–
greyfadeApr 25 '11 at 16:00

This is often used way which is not too good. I am been penetration tester and usually used this to pass arguments like 999999999999999999 which go smoothly through cast and later cause huge error message when passed to 4 bytes integer storage.
–
Tõnu SamuelJun 25 '12 at 3:53

1

using intval() should prevent this behavior, right?
–
devOpJul 20 '12 at 7:38

3

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

My int very seldom go beyond 100. It's usually an automatically iterated id, and in some cases something else but never huge numbers, as I don't write scientific or financial software. If I did, then it would be a concern. I think this applies to most PHP developers.
–
RolfSep 13 '14 at 14:48

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.

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

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

I am not confused. I am saying that your way to use a class like that is a smell of bad design. Not to mention how bad are static methods, for more information: misko.hevery.com/code-reviewers-guide/… to be honest.
–
dynamicApr 10 '13 at 15:39

1

Oh yes, static methods are generally always a sign of bad design. I was just saying that it had nothing to do with namespacing. However, I believe my tiny library is an exception since the point was simply an illustrative library which 1) would never be extended and 2) would never have unit tests (do to the target audience and size of the codebase). My real database/ORM libraries are all namespace classes which are to be used as real and extendable objects as they should be.
–
XeoncrossApr 10 '13 at 15:47

3

I'd still take static class over free functions. If there was ever a name class I could use \DB as XDB. not so much with free functions. While a purely static class may be a code smell, it is not a guarantee that the design is bad; life is not black and white, neither is code.
–
KrisAug 17 '13 at 6:17

WHERE 1=1 or LIMIT 1 does you no good if they inject ');DROP TABLE users;-- . It's beyond me why people just don't use parameterized ("prepared") statements for this and be done with it.
–
Craig RingerApr 28 '13 at 11:10

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 that PDO 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.

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 read OWASP 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.

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:

Update2:

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:

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 is correct and valid approach.

This is absolutely pointless answer. 1. SELECT-based injection is a disaster alone. So, #1 is quite useless. 2. Data filtering won't help for the most of real life usage. Imagine one were used on Stack Overflow - this answer just were unable to happen, as it's full of "evil" words like SELECT, UNION and even complete "malicious" BENCHMARK query. Thus, #2 is inapplicable too. 3. "Use other tools" is not a protection measure at all. So, #3 is as pointless as other two.
–
Your Common SenseMar 19 '13 at 6:08

8

This answer is bad! YourCommonSense outlined most of the reasons, but I'm throwing a downvote in for the thought that beginners can't use a prepared statement. Ridiculous. I would argue that it's probably easier to use prepared statements than concatenate garbage into a query in the first place. You should delete your answer.
–
BradJun 14 '13 at 6:19

3

"It's clear that prepared statement also escaping the data, nothing else" - not really. What about formatting for different types? How do you write a date, or a boolean? Even if SQL injection wasn't an issue, I'd still use parameterized queries.
–
KobiSep 12 '13 at 14:16

I got it. But: you said "beginners, if they can't quickly implement [...] prepared statement". I'm arguing that Brad is right in his (little rude) comment - SQL parameters are much easier than building an SQL string - even ignoring security. Another benefit is that the query can easily be configured, or a const somewhere.
–
KobiSep 12 '13 at 14:23

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:

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.

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 in PHP, and this code add backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a. Pass the input values as parameters to minimize the chance of SQL injection.

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:

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.

It does not solve injection problem, not even slightest. Even only SELECT rights are harmful. This is not a solution but rather a placebo. One could use it for whatever else purpose but isql injection protection.
–
Your Common SenseFeb 18 '13 at 6:36

16

Operating with minimum privileges is a useful damage control strategy to make life harder for the attacker and limit the damage they can do, but it's a bit like saying "my house is on fire, but it's fine, I keep the kerosene locked safely in the garage." Your house is still on fire.
–
Craig RingerApr 28 '13 at 11:06

2

@CraigRinger My house is not on fire. I have no damage and thus no need to "minimize" it. Anyway, the question is "How to prevent a fire", not "How to let my house in fire but minimize the damage".
–
Your Common SenseAug 1 '13 at 6:56

2

Nevertheless, this is not the exact solution to the question yet it is a must. Never give a client privileges more than needed. And you can never know that at some point, someone in your team creates a vulnerability and you realize that client that interacts with the db had full query privileges.
–
Kemal DağOct 6 '13 at 9:57

Im using Active Record with CI too. No problems so far (3 years)
–
Steve MusterJun 25 '13 at 23:45

1

@SteveMuster well, that's good for you. Unfortunately, I have to use full SQL, not such a limited subset offered by CI. Say, sometimes I have run not just insert-and-forget but INSERT IGNORE or INSERT DELAYED. Not just selects but index hinting and such. And so on.
–
Your Common SenseJul 21 '13 at 5:37

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's IP 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.

I am afraid that admin would rather just delete this answer. This approach is just impractical. it's impossible to catch ALL the patterns using mod_rewrite rules. Not to mention that some of them can be perfectly legit. And what about POST requests?
–
Your Common SenseApr 4 '13 at 10:27

I'm referring to a query string that's often used by search engines, and giving some tips to catch the hacker tools that are looking for SQL vulnerability via query string. And mostly, to catch their sensitive informations. About POST requests? Let the PHP language do the job..
–
ServantApr 4 '13 at 11:31

4

that's the point. As PHP the language will do the job anyway, all this mod_rewrite unreliable magic become useless.
–
Your Common SenseApr 4 '13 at 11:35

I'm just trying to answer what the user's asking.. At least RewriteEngine can able to catch it.
–
ServantApr 4 '13 at 11:42

5

Hacking back attacker is a not acceptable approach, especially on this site - and impractical, as @YourCommonSense says. I recommend to modify your answer to something "try to ban them out from your site". Because that is more acceptable purpose.
–
Gabor GaramiApr 21 '13 at 15:11

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.

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. This preg_replace_callback version doesn't cause problems if the replacement contains these tokens.

A hint. You could use sprintf() function which will not only parse a query for you, but also let you choose format manually (say, your function will fail if LIMIT clause parameter will be passed from request query string). In fact, you shouldn't trust to variable type. That's why your approach will never get any followers.
–
Your Common SenseFeb 18 '14 at 20:45

1

@YourCommonSense Hmmm sprintf is useful. But not necessarily here, as strings need to be properly escaped and wrapped with quotes. If the caller casts the parameter to int it won't get quoted so it can be used with the LIMIT clause as well.
–
CalmariusFeb 18 '14 at 22:05

So, it's the problem - "if caller casts". that's what I am talking about - what's the use of the function that require whatever extra actions?
–
Your Common SenseFeb 18 '14 at 22:19

@YourCommonSense Whatever SQL preparing library you use you'll need to tell it somehow how to handle the supplied parameters. If it maps to the SQL prepare statement, you still need to tell it whether it should quote the value or not (string or number). Otherwise it will need to parse the statement and decide based on that. Anyway MySQL is happy if you quote everything.
–
CalmariusJun 14 '14 at 10:07