Am I doing MySQL PDO secure?

[kyme]

I'm trying to display a data from DB using mysqli.

I'm not sure if I'm doing it right, my concern was secure. Am I doing it right?

<?php
include_once('user.php');

// Inialize session
session_start();

$con=mysqli_connect("localhost","root","root","dbname");
if (mysqli_connect_errno())
{
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT aboutme FROM testing where id=1");

if ($result) {
    if ($row = mysqli_fetch_array($result, MYSQL_ASSOC))
    {
        $aboutme = $row['aboutme'];
    }
}  

mysqli_close($con);
?>

[Jacques1]

First off: This is mysqli, not PDO.

 

No, your code is not secure, because you print the MySQL error right on your website for everybody to see. This will leak critical information about your database system.

 

Never print internal errors. They are meant for you (or the server admin), not your users. In fact, forget about manual error handling. Simply enable exceptions and leave the error reporting to PHP:

// enable mysqli exceptions 
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$database_connection = mysqli_connect( ... );

Besides that, your static example query is far too trivial to discuss your understanding of mysqli. Security comes into play when you're dealing with dynamic queries. That's when you need security mechanisms like prepared statements.

Edited May 26, 2016 by Jacques1

[NotionCommotion]

I would go so far to say "always use PDO's prepared statements until you know why you should do differently".

[QuickOldCar]

I agree with others to use pdo and prepared statements. It's just easier and can always be done without many steps and special circumstances.

 

Your query was created by yourself and not using a dynamic value...so doesn't really matter much. Doing a query for 1 is safe.

"SELECT aboutme FROM testing where id=1"

 

If you were to be using from saved data, REQUEST, POST, GET, COOKIE or SESSION then it's very important to never trust the input.

 

A quick example.

$id = $_GET['id'];

$result = mysqli_query($con,"SELECT aboutme FROM testing where id=$id");

 

Now it's whatever someone can type in the address bar is directly inserted into the query. Which is bad unless escaped or checked for values you allow or expect.

 

Can use mysqli_real_escape_string()

 

$id = mysqli_real_escape_string($con, $id);

$result = mysqli_query($con,"SELECT aboutme FROM testing where id=$id");

 

Can also check to see if that dynamic variable is always a digit and not perform a query if is not one. Instead send them a message or some action.

 

Is a pile of filters for sanitizing and validating plus other built in functions can use to check against the variable to ensure you are getting the correct type of data you expect.

http://php.net/manual/en/ref.var.php

[Jacques1]

Is a pile of filters for sanitizing and validating plus other built in functions can use to check against the variable to ensure you are getting the correct type of data you expect.

http://php.net/manual/en/ref.var.php

 

Formal validity doesn't imply safety. For example, a perfectly valid e-mail address can still be used for SQL injection attacks, simply because the address format was never meant to be SQL-safe.

 

Another problem is that validation (as well as escaping) is performed by the application rather than the database system itself, and the application may interpret the input differently. For example, if there's a mismatch between the character encodings, the exact same input can look completely harmless to the application but still wreak havok in the database system.

 

Last but not least, a lot of data cannot be validated at all (like this text), so you'd have to constantly switch between different security strategies. This makes the approach very fragile. Many programmers cannot even get a single strategy right, so I wouldn't necessarily trust them to manage two or three at the same time.

 

So prepared statements are really the only valid approach, and I'd stay away from anything else. The PHP filter library in particular often causes more harm than good, because it's weirdly primitive and deceptive. For example, FILTER_SANITIZE_STRING sounds like it somehow makes every string safe for every context, but in reality it's just a horribly broken HTML tag mangler.

[mac_gyver]

Can use mysqli_real_escape_string()

 

$id = mysqli_real_escape_string($con, $id);

$result = mysqli_query($con,"SELECT aboutme FROM testing where id=$id");

 

 

for this specific case, msyqli_real_escape_string() does NOT protect against sql injection. the value isn't being used in a string context in the sql query statement. sql can be injected that contains absolutely no characters that mysqli_real_escape_string() or any other escape string function operates on, i.e. a hexadecimal encoded string, that the mysql database engine happily converts back to any injected sql it contains.

 

this is typically used to inject sql that satisfies the current SELECT query, than appends a UNION query to select anything from any table the current db connection allows.

[gizmola]

People often confuse escaping with SQL injection.

 

They have nothing to do with each other. Escaping is about taking care of the characters that SQL uses to begin and end a string. Ok, so if your injection is based on injecting a string then it will help with that, but with PDO or mysqli, and a prepared statement, you don't have to bother with escaping. Don't ever create a sql statement that includes a string, and use a parameter instead, and you eliminate sql injection as well as make it unnecessary to use an escaping function.