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) ?
Project concept needs some advice
Moderator: General Moderators
- Jammerious
- Forum Commoner
- Posts: 59
- Joined: Sat Jun 27, 2009 11:30 am
- Location: Slovenia (EU)
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Project concept needs some advice
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().
But yes, if you are doing "SELECT * FROM users WHERE active=1" then you can just use query().
(#10850)
- Jammerious
- Forum Commoner
- Posts: 59
- Joined: Sat Jun 27, 2009 11:30 am
- Location: Slovenia (EU)
Re: Project concept needs some advice
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? =)
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? =)
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Project concept needs some advice
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)
- kaisellgren
- DevNet Resident
- Posts: 1675
- Joined: Sat Jan 07, 2006 5:52 am
- Location: Lahti, Finland.
Re: Project concept needs some advice
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.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().
- Jammerious
- Forum Commoner
- Posts: 59
- Joined: Sat Jun 27, 2009 11:30 am
- Location: Slovenia (EU)
Re: Project concept needs some advice
You know I just realized it's kind of awkward counting DB queries with PDO. While my database class that extends PDO does...
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?
Code: Select all
public function query(&$sql){
$this->dbq++;
return parent::query($sql);
}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
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)
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.
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;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.