Project concept needs some advice

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

User avatar
Jammerious
Forum Commoner
Posts: 59
Joined: Sat Jun 27, 2009 11:30 am
Location: Slovenia (EU)

Re: Project concept needs some advice

Post by Jammerious »

arborint, or anyone else for that matter,
I was reading the manual about PDO and prepared statements. If I understood this correctly they should be used,
when you have user inputs for the query and
when you execute the same query more than one time in a script runtime, that is after you open and before you close the link.
Otherwise, I could or should just use $db_link->query($sql) ?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Project concept needs some advice

Post by Christopher »

Yes, prepared statements are to bind and auto-escape values to SQL statements. Be careful be cause user supplied values can come from more places than just the HTTP request. Dangerous values can even be saved, then reloaded from the DB.

But yes, if you are doing "SELECT * FROM users WHERE active=1" then you can just use query().
(#10850)
User avatar
Jammerious
Forum Commoner
Posts: 59
Joined: Sat Jun 27, 2009 11:30 am
Location: Slovenia (EU)

Re: Project concept needs some advice

Post by Jammerious »

arborint,
this is a very good point. But how can a script be broken by something that is read from the DB? I mean, you select the data and assign it to a variable. If you are going to output, you should use htmlentities(). I am missing something, am I? =)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Project concept needs some advice

Post by Christopher »

Well ... you can quote a string from the user and store it in the database. The later read it out with some XSS or SQL injection attack still present and then use the value again, but not quote it -- thinking it is safe because it came from the database.
(#10850)
User avatar
kaisellgren
DevNet Resident
Posts: 1675
Joined: Sat Jan 07, 2006 5:52 am
Location: Lahti, Finland.

Re: Project concept needs some advice

Post by kaisellgren »

Jammerious wrote:But how can a script be broken by something that is read from the DB? I mean, you select the data and assign it to a variable. If you are going to output, you should use htmlentities().
Reading data from the database, applying htmlentities() with the right parameters and outputting the results inside an element would be XSS safe. This is called Output Encoding. You encode prior to outputting something. I think arborint just wanted to state that data stored almost anywhere can be dangerous. That's true. For example, I once faced an application that read Apache access log and displayed it directly to the user which is insecure. All I needed to do is to access the site with a maliciously constructed URL and I could successfully achieve XSS attacks.
User avatar
Jammerious
Forum Commoner
Posts: 59
Joined: Sat Jun 27, 2009 11:30 am
Location: Slovenia (EU)

Re: Project concept needs some advice

Post by Jammerious »

You know I just realized it's kind of awkward counting DB queries with PDO. While my database class that extends PDO does...

Code: Select all

    public function query(&$sql){
        $this->dbq++;
        return parent::query($sql);
    }
I have no way of doing this when I prepare statements for uses in loops, since the prepare function returns a PDOStatement and this is then out of the DBclass control.
I tried using $dbLink->queryIncrease(); for each execution of the statement, but it kind of sucks... You have to remember to write it and it's making codes longer...
Any advice? :)
User avatar
m4rw3r
Forum Commoner
Posts: 33
Joined: Mon Aug 03, 2009 4:19 pm
Location: Sweden

Re: Project concept needs some advice

Post by m4rw3r »

I made some tests of prepared statements (I don't have the benchmark, as it just was a quick test) and prepared statements are slower on one-of-a-kind queries (per request).
The reason behind this is that a normal query only sends a single query, while a prepared statement does something like this:
(Executing 4 queries)

Code: Select all

PREPARE query_name FROM “SELECT * FROM users WHERE id = ?”?;
SET @var = 1?;
EXECUTE query_name USING @var?;
SET @var = 2?;
EXECUTE query_name USING @var?;
SET @var = 3?;
EXECUTE query_name USING @var;
?SET @var = 4?;
EXECUTE query_name USING @var;
?DEALLOCATE query_name;
As you can see, the smallest amount of "queries" are 4: prepare the query, set the variable(s) to use, execute the query and finally removing it.

And I can agree that MySQLi (or mysql) is easier to code with and faster (when extending the database functionality).
PDO is a few percent faster when creating StdClass objects (PHP 5.3) but it is very slow when creating objects of a user defined class.
Post Reply