Solved

PHP PDO security

Posted on 2015-01-29
3
154 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 108

Accepted Solution

by:
Ray Paseur earned 500 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 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now