Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PHP PDO security

Posted on 2015-01-29
3
Medium Priority
?
198 Views
Last Modified: 2015-02-03
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
Comment
Question by:Pedro Chagas
  • 2
3 Comments
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 40578573
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
 
LVL 3

Author Comment

by:Pedro Chagas
ID: 40585817
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
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 2000 total points
ID: 40586017
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
The viewer will learn how to dynamically set the form action using jQuery.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question