How to Prevent a SQL Injection Escaping Strings

Java - escape string to prevent SQL injection

PreparedStatements are the way to go, because they make SQL injection impossible. Here's a simple example taking the user's input as the parameters:

public insertUser(String name, String email) {
   Connection conn = null;
   PreparedStatement stmt = null;
   try {
      conn = setupTheDatabaseConnectionSomehow();
      stmt = conn.prepareStatement("INSERT INTO person (name, email) values (?, ?)");
      stmt.setString(1, name);
      stmt.setString(2, email);
      stmt.executeUpdate();
   }
   finally {
      try {
         if (stmt != null) { stmt.close(); }
      }
      catch (Exception e) {
         // log this error
      }
      try {
         if (conn != null) { conn.close(); }
      }
      catch (Exception e) {
         // log this error
      }
   }
}

No matter what characters are in name and email, those characters will be placed directly in the database. They won't affect the INSERT statement in any way.

There are different set methods for different data types -- which one you use depends on what your database fields are. For example, if you have an INTEGER column in the database, you should use a setInt method. The PreparedStatement documentation lists all the different methods available for setting and getting data.

How to prevent a SQL Injection escaping strings

You need to use parameters. Well dont have to but would be preferable.

SqlParameter[] myparm = new SqlParameter[2];
myparm[0] = new SqlParameter("@User",user);
myparm[1] = new SqlParameter("@Pass",password);

string comando = "SELECT * FROM ANAGRAFICA WHERE E_MAIL=@User AND PASSWORD_AZIENDA=@Pass";

Can this simple String escaping prevent any SQL Injections?

One method of attack would be by "loading" the attack.

First, you inject as user name, bank transfer message, whatever into it

transfer 0.01

to: 02020.020202.200202

name: johnny tables';drop table foobar --

will be escaped to

johnny tables\';drop table foobar  --

So far so good. protection in effect. our attach failed. We try the loading attack.

Now we are going to make a scheduled payment order.

This is assuming a common error is made, that once inserted in the database, the value is "safe" because it has been checked once.

transfer 0.01

to: 02020.020202.200202

name: johnny tables';drop table foobar--

schedule: 1 day from now

Storing the order in the db

'johnny tables\';drop table foobar--'

will be stored as

johnny tables';drop table foobar--

Now at midnight the scheduler kicks in and starts iterating the scheduled payments

select name from scheduled where time > x and < y

so the bank code starts to chrunch

String name = result['name'];
String acct = result['acct'];
String amt = result['amt'];
string query = "insert into payment_process (name,acct,amt) values('"+name+"','"+acct+"','"+amt+"');

and boom, your table is dropped. *

When you go the manual route, you have to ensure all, each and every instance of the variable is escaped, that all unicode characters are accounted for, that all idiocrancies of the database engine are accounted for.

Also, using prepared statements can give a significant speed boost, because you don't have to rebuild queries. You can just build them once, store them in a cache and just swap out the parameters.

Especially when iterating large lists they are a godsent.

The root problem is that he probably doesn't understand prepared statements, doesn't get them how they work. Insecurity triggered can make aggressive and protective of a certain way, even fanatical, just to prevent to admit you just don't know how they work.

Try to talk to him about it, if he doesn't wish to listen to reason go to his manager and explain the issue, and that if the site/app gets hacked, it will be on the head of your co-worker and your manager, and tell him the risks are HUGE. Point to the recent hacks where a lot of money was stolen like the swift hack.

* May not actually work, dependent on actual query, joins, unions etc.. it's a very simplified example

SQL injection that gets around mysql_real_escape_string()

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 you against this.
The fact that you use single quotes (' ') around your variables inside your query is what protects you against this. The following is also an option:

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

Can I protect against SQL injection by escaping single-quote and surrounding user input with single-quotes?

First of all, it's just bad practice. Input validation is always necessary, but it's also always iffy.

Worse yet, blacklist validation is always problematic, it's much better to explicitly and strictly define what values/formats you accept. Admittedly, this is not always possible - but to some extent it must always be done.

Some research papers on the subject:

http://www.imperva.com/docs/WP_SQL_Injection_Protection_LK.pdf
http://www.it-docs.net/ddata/4954.pdf (Disclosure, this last one was mine ;) )
https://www.owasp.org/images/d/d4/OWASP_IL_2007_SQL_Smuggling.pdf (based on the previous paper, which is no longer available)

Point is, any blacklist you do (and too-permissive whitelists) can be bypassed. The last link to my paper shows situations where even quote escaping can be bypassed.

Even if these situations do not apply to you, it's still a bad idea. Moreover, unless your app is trivially small, you're going to have to deal with maintenance, and maybe a certain amount of governance: how do you ensure that its done right, everywhere all the time?

The proper way to do it:

Whitelist validation: type, length, format or accepted values
If you want to blacklist, go right ahead. Quote escaping is good, but within context of the other mitigations.
Use Command and Parameter objects, to preparse and validate
Call parameterized queries only.
Better yet, use Stored Procedures exclusively.
Avoid using dynamic SQL, and dont use string concatenation to build queries.
If using SPs, you can also limit permissions in the database to executing the needed SPs only, and not access tables directly.
you can also easily verify that the entire codebase only accesses the DB through SPs...

Prevent SQL Injection: Do I need to escape input before binding values into a PDO query

You don't need to escape/convert HTML to avoid a SQL injection if you're using prepared queries. As for when you need to use htmlentities or htmlspecialchars, you will need that if you output user-provided data to a web-page. Not doing that makes you vulnerable to XSS (Cross-Site Scripting) injections, ie. malicious javascript that gets executed in the browser on page load. Recommended reading: XSS Prevention Cheat Sheet and PHP Security Cheat Sheet.

Aside SQL and XSS injection prevention, you may also want to do other checks on user input, such as validating e-mail addresses, username characters, etc. before you input them into your DB. Otherwise you're up for some tedious database cleaning down the road. You may want to look into PHP's filter functions, basic regex that removes everything outside white-listed character ranges, etc.

PostgresSQL - SQL Prepared Statement vs String Escaping preventing SQL injection attacks

My application isn't going to keep the database connection alive therefore the prepared statement is only ever going to be used once

If you are worried about performance, you should fix this single-use connection issue. And if you are not worried about performance, then why do you care if prepared statements are "over-kill"?

While either should work, the first one is cleaner, and less likely for someone to screw up in the future.