The correct way to avoid SQL injection attacks, no matter which database you use, is to separate the data from SQL, so that data stays data and will never be interpreted as commands by the SQL parser. It is possible to create SQL statement with correctly formatted data parts, but if you don't fully understand the details, you should always 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([ '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); // 's' specifies the variable type => 'string'
$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 (for example, 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 are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is:
$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'password');
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
In the above example the error mode isn't strictly necessary, but it is advised to add it. This way the script will not stop with a Fatal Error
when something goes wrong. And it gives the developer the chance to catch
any error(s) which are throw
n as PDOException
s.
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).
Although you can set the charset
in the options of the constructor, it's important to note that 'older' versions of PHP (before 5.3.6) silently ignored the charset parameter in the DSN.
Explanation
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 of 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([ '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', otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
$dir = 'ASC';
}
You can't.
There are steps you can take to make it a little more difficult, but ultimately any executable on the local machine is crackable. Eventually, that code has to be converted into native machine code and every application that is runnable is vulnerable.
What you want to do is just make it difficult enough to crack to make it not worth peoples' trouble.
Some suggestions I have for you to help protect your application:
- Obfuscate your code. Dotfuscator has a free edition and comes with Visual Studio.
- Use public/private key or asymmetric encryption to generate your product licenses. This ensures that only you can generate your license codes. Even if your application is cracked, you can be sure that they won't be releasing a key generator for your application, because it is impossible to reverse the key generating algorithm.
- Use a third-party packer to pack your .NET executable into an encrypted Win32 wrapper application. Themida is one of the better ones. This stops people from reflecting your application in .NET Reflector and makes it a pain to unpack for reversing.
- Write your own custom packer. If the third-party packers are too expensive, consider writing your own. Sometimes custom packers can be very effective, because there aren't well published methods on how to unpack them. The tutorial How to write your own packer gives a ton of good information on writing your own Win32 packer.
Ultimately though, if people want your application cracked they will. Look at all the commercial software out there that has a vast amount of resources to protect their applications and yet they are cracked before the applications are even released to the public.
A skilled reverse engineer can fire up IDA-Pro and slice through your application like butter no matter what you do. A packed application can be unpacked and obfuscation only prevents it from making it a walk in the park. All your hard work with your complex license code can be undone with a single byte patch.
You just need to accept that there is a very real chance people are going to pirate your software. There are some people who are never going to pay for your application no matter what and these are the people you don't need to worry about.
There are however, many businesses out there who would never risk a lawsuit and happily buy software licenses and many computer users who either don't want to risk it, find it wrong or are not tech savvy enough to pirate. These are your true customers, and you should focus your efforts on providing them with a good user experience and ignore the people cracking your software.
I've had my application pirated before, and I took it as a personal affront. Here I was, a small-time developer, pouring my heart and soul into an application and these people had the gall to pirate from me?! They were taking money directly from my pocket!
I immediately added in a bunch of draconian DRM code and attempted to sabotage any person using an illegitimate or cracked copy. I should of course have been working on making my application better instead of trying to stop the inevitable. Not only that, but I was hurting my true customers will all these extra protections I was putting in.
After a long battle I realized I was fighting the tides and all this time wasted was for naught. I took out all the phone-home code except for the barebones license functions and never looked back.
Best Answer
AFAIK, there is not any trick for complete avoidance of reverse engineering.
And also very well said by @inazaruk: Whatever you do to your code, a potential attacker is able to change it in any way she or he finds it feasible. You basically can't protect your application from being modified. And any protection you put in there can be disabled/removed.
You can do different tricks to make hacking harder though. For example, use obfuscation (if it's Java code). This usually slows down reverse engineering significantly.
As everyone says, and as you probably know, there's no 100% security. But the place to start for Android, that Google has built in, is ProGuard. If you have the option of including shared libraries, you can include the needed code in C++ to verify file sizes, integration, etc. If you need to add an external native library to your APK's library folder on every build, then you can use it by the below suggestion.
Put the library in the native library path which defaults to "libs" in your project folder. If you built the native code for the 'armeabi' target then put it under libs/armeabi. If it was built with armeabi-v7a then put it under libs/armeabi-v7a.