Solved

PHP PDO security

Posted on 2015-01-29
3
157 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 109

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 109

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sort in mysql based off of query param 4 24
error log using ftp 7 40
two ways encryption with php 3 27
ORA-02288: invalid OPEN mode 2 28
Creating and Managing Databases with phpMyAdmin in cPanel.
Read about achieving the basic levels of HRIS security in the workplace.
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…
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…

770 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