Page 2 of 2

Re: Project concept needs some advice

Posted: Sat Jul 25, 2009 5:13 pm
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) ?

Re: Project concept needs some advice

Posted: Sun Jul 26, 2009 12:15 pm
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().

Re: Project concept needs some advice

Posted: Mon Jul 27, 2009 5:55 am
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? =)

Re: Project concept needs some advice

Posted: Mon Jul 27, 2009 6:21 pm
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.

Re: Project concept needs some advice

Posted: Sun Aug 02, 2009 1:22 am
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.

Re: Project concept needs some advice

Posted: Mon Aug 03, 2009 5:42 pm
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? :)

Re: Project concept needs some advice

Posted: Tue Aug 04, 2009 5:49 am
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.