SQL Injections in Adodb and General Website Security

SQL injections in ADOdb and general website security

SQL injection attacks happen when user input is improperly encoded. Typically, the user input is some data the user sends with her query, i.e. values in the $_GET, $_POST, $_COOKIE, $_REQUEST, or $_SERVER arrays. However, user input can also come from a variety of other sources, like sockets, remote websites, files, etc.. Therefore, you should really treat everything but constants (like 'foobar') as user input.

In the code you posted, mysql_real_escape_string is used to encode(=escape) user inputs. The code is therefore correct, i.e. does not allow any SQL injection attacks.

Note that it's very easy to forget the call to mysql_real_escape_string - and one time is enough for a skilled attacker! Therefore, you may want to use the modern PDO with prepared statements instead of adodb.

How to prevent SQL injections in manually created queries?

I dont want to use other method

You should use whatever provides the required functionality, not the method that you like more over others!

Also you should never access superglobals directly in CakePHP, this will only bring you in trouble, especially in unit tests. User the proper abstracted methodes provided by the request object, that is CakeRequest::query().

Cookbook > Controllers > Request and Response objects > Accessing Querystring parameters

---

Use prepared statements

That being said, use prepared statements, either by passing the values to bind to the second argument of Model::query():

$result = $this->Search->query(
    "select * from subcategories where subcat_name like ? and subcat_status='active'",
    array('%' . $this->request->query('searchkey') . '%')
);

API > Model::query()

or by using DboSource::fetchAll(), which accepts parameters as the second argument too:

$db = $this->Search->getDataSource();
$result = $db->fetchAll(
    "select * from subcategories where subcat_name like ? and subcat_status='active'",
    array('%' . $this->request->query('searchkey') . '%')
);

• Cookbook > Models > Retrieving Your Data > Prepared Statements
• API > DboSource::fetchAll()

Escape manually

For the sake of completeness, it's also possible to manually escape the value via DboSource::value(), however you should avoid constructing query strings that way at all costs, as a small mistake can end up causing an unescaped value to be inserted, thus creating a possible SQL injection vulnerability:

$searchkey = $this->request->query('searchkey');

$db = $this->Search->getDataSource();
$value = $db->value('%' . $searchkey . '%', 'string');

$result = $this->Search->query(
    "select * from subcategories where subcat_name like $value and subcat_status='active'"
);

API > DboSource::value()

Is SQL injection a risk today?

Quite the contrary. Magic quotes are deprecated in PHP5 and will be completely removed in PHP 5.4, as they brought more confusion to the programming world than they did good. Checking whether magic quotes are active, and escaping any SQL input scrupulously if necessary, is still very, very important... No reason to feel bad though, we've all been there, and my unknowing ass has been saved by magic quotes countless times :)

The PHP manual on magic quotes explains everything.

SQL injection on INSERT

Injection can happen on any SQL statement not run properly.

For example, let's pretend your comment table has two fields, an integer ID and the comment string. So you'd INSERT as follows:

 INSERT INTO COMMENTS VALUES(122,'I like this website');

Consider someone entering the following comment:

'); DELETE FROM users; --

If you just put the comment string into the SQL without any processesing this could turn your single INSERT in to the following two statements followed by a comment:

INSERT INTO COMMENTS VALUES(123,''); DELETE FROM users; -- ');

This would delete everything from your users table. And there are people willing to spend all day finding the right tablename to empty using trial and error and various tricks. Here's a description of how you could perform an SQL Injection attack.

You need to use parameterized SQL statements to prevent this.

And this isn't just for security reasons. For example, if you're creating your SQL statements naively the following comment:

I'm just loving this website

would cause an SQL syntax error because of the apostrophe being interpreted by SQL as a closing quote.

Does asp.net protect against sql injection attacks

No. As long as you're supplying the SQL, it's up to you to be smart in how you use the controls.

That usually means sanitizing input and using Parameterized Queries or Stored Procedures over dynamic SQL strings.

If the control is generating the queries for you (like the Membership Controls, etc.) then you're well protected.

DB_PREFIX and security against SQL injections and other attacks

Having a custom prefix in all application tables can serve two purposes:

• Sharing the same database with other apps. That's something that only makes sense with really cheap hosting services—and in 2017 that means a really low-cost service.

• Having a multi-tenant application (a single app that splits its data across several copies of the same table set). These kind of apps tend to be a maintenance nightmare.

It can't possibly add any extra security because it doesn't attempt to. It isn't a security feature at all. In fact, for the security standpoint it could be argued that it can actually increase risks. If you are sharing storage with some other arbitrary application it's very likely that you're also sharing the same MySQL user with the same read and write privileges on the complete database. In such case, the other app can easily wipe out your tables, either intentionally or by mistake.

About the good practice part... That's always fairly subjective. My personal opinion is that it provides a dubious benefit (sharing a database is like sharing bath water, it only makes sense if you live in a desert) and it makes writing SQL code more annoying. For instance, you can no longer copy and paste between your PHP code and your MySQL client and, if you use an IDE that recognises SQL inside PHP strings, you'll no longer get proper code intelligence.

General website technical security

It looks like you have address less than half of the OWASP TOP 10. Most importantly Insecure Direct Object Reference and insufficient transport layer protection because these are very commonly used by attackers. Also don't forget about Clickjacking.

Are Parameters really enough to prevent Sql injections?

Placeholders are enough to prevent injections. You might still be open to buffer overflows, but that is a completely different flavor of attack from an SQL injection (the attack vector would not be SQL syntax but binary). Since the parameters passed will all be escaped properly, there isn't any way for an attacker to pass data that will be treated like "live" SQL.

You can't use functions inside placeholders, and you can't use placeholders as column or table names, because they are escaped and quoted as string literals.

However, if you use parameters as part of a string concatenation inside your dynamic query, you are still vulnerable to injection, because your strings will not be escaped but will be literal. Using other types for parameters (such as integer) is safe.

That said, if you're using use input to set the value of something like security_level, then someone could just make themselves administrators in your system and have a free-for-all. But that's just basic input validation, and has nothing to do with SQL injection.

---

Related Topics