Issues regarding Mysql Injection and PDO

[tillabong]

Hi i've came across several threads but couldnt really find the answers. there are quite a few doubts i have regarding mysql injections. pardon me if the questions seem too easy or stupid. here are some examples.

1. Are queries comparatively safer from sql injections if the parameters are bounded by quotes?

$id = mysql_real_escape_string($_POST['id']);

$query = " SELECT names FROM table WHERE id = '$id' ";  /* as compared to */

$query = " SELECT names FROM table WHERE id = $id "; 

if i try to add something like $id = ' id; DROP table ', the query without the quotes around the variable would have been vulnerable. the query with the quotes would be harder to break with something like $id = ' x'; DROP table '. But since $id has already been sanitised. it wouldnt happen right?


2. if i use mysql_query($query), sql injection is greatly reduced?

Using the previous example if i were to try to do an injection, it wouldnt be possible right? Since mysql_query only allows a single query and the DROP table part i injected wouldnt be sent to the server?

3. Are queries with no user input vulnerable to SQL injections?
For example,

$query = " SELECT names FROM table WHERE id = '1' "; 

------------------------------------------
PDO Questions.
4. Are PDO statements vulnerable?

try {
    $dbh = new PDO("mysql:host=localhost;dbname=database", "user", "password");} 
catch(PDOException $e) 
    {Header("Location:/error.php");}

$id = mysql_real_escape_string($_POST['id']);

$query = $dbh->prepare (' SELECT email FROM table WHERE id = :id ');
$query->bindParam( ':id' ,  $id );
$query->execute(); 

if

$id = ' 1; INSERT INTO memberslist (email, ID) VALUES (hi, 1) ' 

. Is this possible to execute in a PDO statement? i have tried it but it doesnt work. so im not sure whether it is possible. if it was possible, with reference to question 1, would it be safer to bound the parameters by quotes like this

$query = $dbh->prepare (' SELECT email FROM table WHERE id = ":id" '); 

5. Is there a need to catch exceptions from pdo statements since if there was a failure to connect to the database in the first place, the first exception would have been caught and redirected to the error page?

6. Is it feasible or productive if i use PDO statements together with normal queries like mysql_query(). Since i would have to connect again using mysql_connect(). or is it more productive if i only used one type of method?

Sorry if its abit long. Thank you very much for your time.

[as4s1n]

1. Are queries comparatively safer from sql injections if the parameters are bounded by quotes?

$id = mysql_real_escape_string($_POST['id']); 

$query = " SELECT names FROM table WHERE id = '$id' ";  /* as compared to */ 

$query = " SELECT names FROM table WHERE id = $id "; 

if i try to add something like $id = ' id; DROP table ', the query without the quotes around the variable would have been vulnerable. the query with the quotes would be harder to break with something like $id = ' x'; DROP table '. But since $id has already been sanitised. it wouldnt happen right?

1) There should be no difference. mysqi_real_escape_string() does a pretty good job of taking care of SQL injections but PDO handles it cleaner.

2. if i use mysql_query($query), sql injection is greatly reduced?

2) I don't think that makes a difference, but how do you initiate your query without mysql_query()? It reduces the chance but nothing is 100% safe, it gets close.

3. Are queries with no user input vulnerable to SQL injections?
For example,

$query = " SELECT names FROM table WHERE id = '1' "; 

3) Not at all, dynamic queries which need a user input are only vulnerable to SQL Injection

4. Are PDO statements vulnerable?

try { 
    $dbh = new PDO("mysql:host=localhost;dbname=database", "user", "password");}  
catch(PDOException $e)  
    {Header("Location:/error.php");} 

$id = mysql_real_escape_string($_POST['id']); 

$query = $dbh->prepare (' SELECT email FROM table WHERE id = :id '); 
$query->bindParam( ':id' ,  $id ); 
$query->execute(); 

if

$id = ' 1; INSERT INTO memberslist (email, ID) VALUES (hi, 1) ' 

. Is this possible to execute in a PDO statement? i have tried it but it doesnt work. so im not sure whether it is possible. if it was possible, with reference to question 1, would it be safer to bound the parameters by quotes like this

$query = $dbh->prepare (' SELECT email FROM table WHERE id = ":id" '); 

4) Yes, but you can lower the risk of SQL injection using the PDO::PREPARE() to set up and allow for easy parsing for multiple queries and PDO::EXECUTE() to run it. And you cannot use mysqli_real_escape_string() in PDO unless you have a mysqli db connection in which case you won't be using PDO.

if

$id = ' 1; INSERT INTO memberslist (email, ID) VALUES (hi, 1) ' 

. Is this possible to execute in a PDO statement? i have tried it but it doesnt work. so im not sure whether it is possible. if it was possible, with reference to question 1, would it be safer to bound the parameters by quotes like this

If you remove the 1; (why is that there by the way?) then you can insert that into a PDO prepare like:

$query = $dbh->prepare($id); 

5. Is there a need to catch exceptions from pdo statements since if there was a failure to connect to the database in the first place, the first exception would have been caught and redirected to the error page?

5) I am not sure how it would work with redirecting. My guess would be it would be unnecessary if no one is going to see it, but if it is an error that you don't expect and want to fix you could have the catch write to an errors file.

6. Is it feasible or productive if i use PDO statements together with normal queries like mysql_query(). Since i would have to connect again using mysql_connect(). or is it more productive if i only used one type of method?

6) PDO is better overall, while I'm pretty sure you can only connect using one, which you define at the dbh. It would probably be more productive because it is more streamline and easier to use.

[misson]

pardon me if the questions seem too easy or stupid.

You're learning, and that's the important thing. Stupidity is the result of remaining ignorant, so never stop studying.

1. Are queries comparatively safer from sql injections if the parameters are bounded by quotes?

Quotes around a value (when the value is interpolated directly into the query) are necessary to make a query safe, since quotes are what separates a value from the rest of the statement. No quotes and the statement isn't safe. With prepared statement parameters, values are sent separate from the statement, so there is no need to delineate the two, so quotes are unnecessary.

Speaking more generally so that we can cover any sort of injection, you can make a distinction between a host language and embedded languages. The processor working on data determines the host language: for MySQL, it's SQL; for PHP (the processor), it's PHP (the language); for a browser layout engine, it's HTML or XML and CSS; for a JS engine, it's JS. Embedded languages are simply any other languages in the data (though they will likely become host languages at some point). For example, both CSS and JS often appear embedded in HTML, and all three are embedded in PHP and JSP. Values in SQL statements and text data in HTML also constitute languages, though often undefined ones and not necessarily the same language across different documents or even within the same document (embedded scripts and CSS are merely text data in HTML, which is why you used to see them commented out with "<!-- ... -->").

We can identify the host and embedded languages even for regular expressions: the embedded language consists of the literal characters, and the host language consists of the metacharacters. For example,

/(\([2-9][0-9]{2}\))[0-9]{3}-\d{4}/

Host and embedded languages, when appearing together in a single document, need some way of being distinguished, usually through the use of delimiters. In the regexp example, note the slashes at the start and end of the regexp are not a part of the regexp; they are delimiters and thus belong to the host language (such as JS or Perl).

Whenever data that's only supposed to hold statements in an embedded language is interpolated into a document and delimiters appear in the interpolated data, you have injection. (The regexp example points to the fact that you can even have regexp injection.) To prevent injection, you have to ensure interpolated data only holds statements from a limited set of languages (sanitization), convert the delimiters to a form where they won't be interpreted as delimiters (escaping) or use another method that keeps statements from multiple languages separate (such as prepared statements and prepared statement parameters).

2. if i use mysql_query($query), sql injection is greatly reduced?

mysql_query doesn't support multiple queries, but those aren't the only type of injected SQL statements. Subqueries are another (though those are limited to SELECT statements in the SQL standard). Lack of support for multiqueries isn't safe enough.

3. Are queries with no user input vulnerable to SQL injections?

Think about what SQL injection means: data is added to an SQL query that gets interpreted as part of the query. If the query is entirely static, injection isn't possible. If the query isn't static, injection is possible.

Analyze injection vulnerabilities entirely in terms of the statement definition. For example,

SELECT id, name FROM $tbl WHERE last_seen > DATE_SUB(NOW(), :period) AND name IN ($names)

$tbl and $names are injection vectors (meaning they can potentially carry injected code, rather like the medical sense of "vector" as "disease carrier"), so they need to be vetted. Depending on their source, they may or may not need sanitization/escaping.

What it really comes down to is a matter of trust, a concept central to computer security: where does the data come from, and how far do you trust that source? Injection attacks arise when you implicitly trust a source. General users should be untrusted. Employees should partially trusted, up to whatever access restrictions they are given in the organization (for a single-admin site, there typically are no restrictions, though even this can be dangerous if the admin's account is hijacked). Trusting employees too much has resulted in data theft. If you trust a source, you also implicitly trust any source that it trusts. This is part of what makes viruses, worms and the like possible and can allow a secure, intranet isolated server to be infected. Data pulled from a database inherits the trust level of the least trusted source that can put data into the database (the details of inheritance are subtle). You can always safely treat data as coming from a lower trust level than it actually does, so a database may be treated as untrustworthy, even if it's actually trustworthy.

4. Are PDO statements vulnerable?

Prepared statement parameters (PDO or otherwise) aren't vulnerable. The statement that a prepared statement comes from is. In the previous example, :period can't be an injection vector, but $tbl and $names can.

$id = mysql_real_escape_string($_POST['id']);
$query = $dbh->prepare (' SELECT email FROM table WHERE id = :id ');
$query->bindParam( ':id' ,  $id ); 

As as4s1n noted, escaping the value passed as a prepared statement parameter is unnecessary. Not only that, it's wrong. You'll wind up with escapes within the stored data.

[misson]

5. Is there a need to catch exceptions from pdo statements since if there was a failure to connect to the database in the first place, the first exception would have been caught and redirected to the error page?

Connection failures aren't the only thing that will cause exceptions, if the error mode of the connection is PDO::ERRMODE_EXCEPTION. If the connection isn't set to throw exceptions, then you'll need to test the result of every query to see whether or not it succeeded. If the connection fails, you won't have a valid PDO object, so the rest of the statements should appear within the try block anyway.

try {
    // returns a PDO w/ errmode set to throw exceptions. connect() might itself throw PDOException
    $db = LocalDB::connect();
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
    
    // the following won't throw exceptions
    if (! ($getProfile = $db->prepare(...)) {
        ...
    } else {
        if (! $getProfile->execute(...)) {
        } else {
            // this query is dependent on the first
            if (! ($updateProfileStats = $db->prepare(...)) {
            } else {
                $updateProfileStats->execute(...);
                ...
            }
    }
    // this query isn't dependent on the first two
    if (! ($query = $db->prepare(...)) {
        ...
    } else {
        $query->execute(...);
        ...
    }
} catch (PDOException $exc) {
    ...
}
// $db may not be valid here, so it shouldn't be used 

If the connection is set to throw exceptions, a single PDOException handler can handle multiple PDO operations.

try {
    // returns a PDO w/ errmode set to throw exceptions. connect() might throw PDOException
    $db = LocalDB::connect();
    
    // these queries are followed by an independent query; catch PDOExceptions so that 
    // $onlineUsers will be executed regardless of the outcome of these next queries
    try {
        $getProfile = $db->prepare(...);
        $getProfile->execute(...);
        $updateProfileStats = $db->prepare(...);
        $updateProfileStats->execute(...);
        ...
    } catch (PDOException $exc) {
        ...
    }
    $onlineUsers = $db->prepare(...);
    $onlineUsers->execute(...);
    ...    
} catch (PDOException $exc) {
    ...
} 

If each query is dependent on the success of previous ones, you only need the outer try block. Unlike the example, all queries in a well designed system will be wrapped in the data access layer, with related queries appearing as parts of the same components ($getProfile and $updateProfileStats are part of profiles, while the $onlineUsers is part of user activity).

6. Is it feasible or productive if i use PDO statements together with normal queries like mysql_query(). Since i would have to connect again using mysql_connect(). or is it more productive if i only used one type of method?

It's perfectly possible, but there's no benefit and some detriment in the form of extra resource usage (though probably not enough to have an impact) and inconsistency, which adds confusion and makes the code harder to develop and maintain. The only real reason to use both drivers (mysql_query is no more normal than what PDO offers) is that you have legacy code that you haven't updated.

[tillabong]

Thank you so much for taking time to reply my questions. i've learnt a great deal. thank you.