Solved

PHP PDO security

Posted on 2015-01-29
3
169 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 110

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 110

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article discusses how to create an extensible mechanism for linked drop downs.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

749 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