Asked  7 Months ago    Answers:  5   Viewed   28 times

Will mysql_real_rescape_string() be enough to protect me from hackers and SQL attacks? Asking because I heard that these don't help against all attack vectors? Looking for the advice of experts.

EDIT: Also, what about LIKE SQL attacks?

 Answers

55

@Charles is extremely correct!

You put yourself at risk for multiple types of known SQL attacks, including, as you mentioned

  • SQL injection: Yes! Mysql_Escape_String probably STILL keeps you susceptible to SQL injections, depending on where you use PHP variables in your queries.

Consider this:

$sql = "SELECT number FROM PhoneNumbers " .
       "WHERE " . mysql_real_escape_string($field) . " = " . mysql_real_escape_string($value);  

Can that be securely and accurately escaped that way? NO! Why? because a hacker could very well still do this:

Repeat after me:

mysql_real_escape_string() is only meant to escape variable data, NOT table names, column names, and especially not LIMIT fields.

  • LIKE exploits: LIKE "$data%" where $data could be "%" which would return ALL records ... which can very well be a security exploit... just imagine a Lookup by last four digits of a credit card... OOPs! Now the hackers can potentially receive every credit card number in your system! (BTW: Storing full credit cards is hardly ever recommended!)

  • Charset Exploits: No matter what the haters say, Internet Explorer is still, in 2011, vulnerable to Character Set Exploits, and that's if you have designed your HTML page correctly, with the equivalent of <meta name="charset" value="UTF-8"/>! These attacks are VERY nasty as they give the hacker as much control as straight SQL injections: e.g. full.

Here's some example code to demonstrate all of this:

// Contains class DBConfig; database information.
require_once('../.dbcreds');                       

$dblink = mysql_connect(DBConfig::$host, DBConfig::$user, DBConfig::$pass);
mysql_select_db(DBConfig::$db);
//print_r($argv);

$sql = sprintf("SELECT url FROM GrabbedURLs WHERE %s LIKE '%s%%' LIMIT %s",
               mysql_real_escape_string($argv[1]),
               mysql_real_escape_string($argv[2]),
               mysql_real_escape_string($argv[3]));
echo "SQL: $sqln";
$qq = mysql_query($sql);
while (($data = mysql_fetch_array($qq)))
{
        print_r($data);
}

Here's the results of this code when various inputs are passed:

$ php sql_exploits.php url http://www.reddit.com id
SQL generated: SELECT url FROM GrabbedURLs 
               WHERE url LIKE 'http://www.reddit.com%'
               ORDER BY id;
Returns: Just URLs beginning w/ "http://www.reddit.com"

$ php sql_exploits.php url % id
SQL generated: SELECT url FROM GrabbedURLs 
               WHERE url LIKE '%%' 
               ORDER BY id;
Results: Returns every result Not what you programmed, ergo an exploit --

$ php sql_exploits.php 1=1 'http://www.reddit.com' id Results: Returns every column and every result.

Then there are the REALLLY nasty LIMIT exploits:

$ php sql_exploits.php url 
> 'http://www.reddit.com'
> "UNION SELECT name FROM CachedDomains"
Generated SQL: SELECT url FROM GrabbedURLs 
               WHERE url LIKE 'http://reddit.com%' 
               LIMIT 1 
               UNION
               SELECT name FROM CachedDomains;
Returns:  An entirely unexpected, potentially (probably) unauthorized query
          from another, completely different table. 

Whether you understand the SQL in the attacks or not is irrevelant. What this has demonstrated is that mysql_real_escape_string() is easily circumvented by even the most immature of hackers. That is because it is a REACTIVE defense mechism. It only fixes very limited and KNOWN exploits in the Database.

All escaping will NEVER be sufficient to secure databases. In fact, you can explicitly REACT to every KNOWN exploit and in the future, your code will most likely become vulnerable to attacks discovered in the future.

The proper, and only (really) , defense is a PROACTIVE one: Use Prepared Statements. Prepared statements are designed with special care so that ONLY valid and PROGRAMMED SQL is executed. This means that, when done correctly, the odds of unexpected SQL being able to be executed are drammatically reduced.

Theoretically, prepared statements that are implemented perfectly would be impervious to ALL attacks, known and unknown, as they are a SERVER SIDE technique, handled by the DATABASE SERVERS THEMSELVES and the libraries that interface with the programming language. Therefore, you're ALWAYS guaranteed to be protected against EVERY KNOWN HACK, at the bare minimum.

And it's less code:

$pdo = new PDO($dsn);

$column = 'url';
$value = 'http://www.stackoverflow.com/';
$limit = 1;

$validColumns = array('url', 'last_fetched');

// Make sure to validate whether $column is a valid search parameter.
// Default to 'id' if it's an invalid column.
if (!in_array($column, $validColumns) { $column = 'id'; }


$statement = $pdo->prepare('SELECT url FROM GrabbedURLs ' .
                           'WHERE ' . $column . '=? ' .
                           'LIMIT ' . intval($limit));
$statement->execute(array($value));
while (($data = $statement->fetch())) { }

Now that wasn't so hard was it? And it's forty-seven percent less code (195 chars (PDO) vs 375 chars (mysql_). That's what I call, "full of win".

EDIT: To address all the controversy this answer stirred up, allow me to reiterate what I have already said:

Using prepared statements allows one to harness the protective measures of the SQL server itself, and therefore you are protected from things that the SQL server people know about. Because of this extra level of protection, you are far safer than by just using escaping, no matter how thorough.

Wednesday, March 31, 2021
 
Skipper
answered 7 Months ago
19

It uses the C API, so in this case there is no chance for SQL injection.

Wednesday, March 31, 2021
 
ChronoFish
answered 7 Months ago
26

The discussion thus far has been about protecting from SQL Injection and Persistent cross site scripting. It sounds like you're on the right track.

  • Your use of prepared statements is a "best practice" to combat SQL injection.
  • htmlspecialchars() is a good start to prevent XSS, but you have to escape data in the encoding scheme that is appropriate to where you are outputting data. OWASP has a comprehensive page that discusses this: XSS (Cross Site Scripting) Prevention Cheat Sheet. The short answer: Ensure you are using "the escape syntax for the part of the HTML document you're putting untrusted data into."
Saturday, May 29, 2021
 
Elxx
answered 5 Months ago
21

No they are not available inside Mono BCL, nor Xamarin.iOS or Xamarin.Android.

There's a bug report for each of them, ECDSA and ECDH, so you might want to add yourself on c.c. to know when this will change. Right now there's no time table to include them.

Thursday, August 19, 2021
 
Timo
answered 2 Months ago
60

You could make the SplashActivity your start activity.

When MyApplication has completed it's work you could start your main activity and dispose the splash screen.

But don't do the heavy database work in onCreate, create another function and do it there, otherwise your splash activity won't be shown.

public class SplashActivity extends Activity
    @override
    protected void onResume() {
    //Create a thread
    new Thread(new Runnable() {
            public void run() {
                //Do heavy work in background
                ((MyApplication)getApplication()).loadFromDb();
                startActivity(new Intent(SplashActivity.this, MainActivity.class));
                finish(); //End this activity
            }
        }).start();
    }
}
Monday, August 23, 2021
 
Craig Bovis
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :