Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

PHP PDO security

Hi E's,
I want access to database using PHP PDO, for avoid sql injection, and I want to know if my code is secure:
 <?
$conn = new PDO(
    'mysql:host=localhost;dbname=Remove', 'remove', 'remove',
  array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8')  
);

$sql = 'SELECT nome
        FROM artigos
        WHERE id = 1
        ORDER BY id';
 
$q = $conn->query($sql);
$r = $q->fetch();
echo $r['nome'];
?>

Open in new window

My big doubt is about this line: "WHERE id = 1". Is correct?
Note, the "id" just accept numeric characters.
My code is ok, or I have to improve them?

The best regards, JC
0
Pedro Chagas
Asked:
Pedro Chagas
  • 2
2 Solutions
 
Ray PaseurCommented:
Yes, this is mostly fine as written and there is no PDO-related danger.  However there is a blank character before the "open PHP" statement, and it's using the short open tags, which are being removed from PHP, so you might want to start the script like this:

<?php

That said, most SQl queries include some kind of external data.  It's important to sanitize this data before using it in a query.  In order to do that you may want to learn about how PHP can help you filter and sanitize external data.
http://php.net/manual/en/book.filter.php

There are also "prepared statements" available in PDO.  This article shows some of the things that may be helpful when you try to use them.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
 
Pedro ChagasWebmasterAuthor Commented:
Thank you Ray.
In my script, the goal is the query just accept numbers, so is good idea sanitize the query, check if the query is a number?

~JC
0
 
Ray PaseurCommented:
The "query" is the entire string shown here:

$sql = 'SELECT nome
        FROM artigos
        WHERE id = 1
        ORDER BY id';

Open in new window

The only part of the query that is a number is "1" so you're OK with the query as written.  If the number comes from an external source, such as a request parameter in the URL, then the value must be sanitized.  Consider this URL:

/path/to/script.php?id=3

Before you would use the value of $_GET['id'] in your query string, you would want to be sure it is an integer.  The code would look something like this:

$id = (int) $_GET['id'];
$sql = "SELECT nome
        FROM artigos
        WHERE id = $id
        ORDER BY id";

Open in new window

The effect of using (int) is to cast the external data as an integer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now