Re: DB code

On 10/17/05, garaged <garaged <at> gmail.com> wrote:
> I haven't see a lot of RC code, but I don't quite see a lot of space
> for prepared queries.
>
> where statements are almost all you need for most applications.
>
> Doing the correct quotation is a good programming pratice, and it wont
> be corrected by prepared queries.
>
> Max
>

Prepared query handlers do the correct quotations for you, if they
don't then it should not be called a prepared query. Prepared queries
to type checking, cache the base query, and other goodies along with
proper escaping/quoting. This is why you would use prepared queries,
so you don't have to worry about escaping user input for fear of
injection exploits.

--
Christopher A. Watford
christopher.watford <at> gmail.com

Re: DB code

garaged wrote:
>>Prepared query handlers do the correct quotations for you, if they
>>don't then it should not be called a prepared query. Prepared queries
>>to type checking, cache the base query, and other goodies along with
>>proper escaping/quoting. This is why you would use prepared queries,
>>so you don't have to worry about escaping user input for fear of
>>injection exploits.
> 
> 
> Do you think is cleaner or easy to understand to do prepared queries
> vs correct quotation??
> 
> You have to remember exactly the correct sequence of parameters for
> every query. I'm not that good with memory, but I migth be one in a
> million.

Thats why I mentioned that MDB2 supports the oracle style :name prepared 
statements. Then you do not have to remember the order and you can 
directly reference things by their name:

See my slides on database abstraction in MDB2 and PDO for details:
http://www.backendmedia.com/MDB2/database_abstraction.pdf

regards,
Lukas

Re: DB code

> Prepared query handlers do the correct quotations for you, if they
> don't then it should not be called a prepared query. Prepared queries
> to type checking, cache the base query, and other goodies along with
> proper escaping/quoting. This is why you would use prepared queries,
> so you don't have to worry about escaping user input for fear of
> injection exploits.

Do you think is cleaner or easy to understand to do prepared queries
vs correct quotation??

You have to remember exactly the correct sequence of parameters for
every query. I'm not that good with memory, but I migth be one in a
million.

Max

--
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GS/S d- s: a-29 C++(+++) ULAHI+++ P+ L++>+++ E--- W++ N* o-- K- w++++
O- M-- V-- PS+ PE Y-- PGP++ t- 5- X+ R tv++ b+ DI+++ D- G++ e++ h+ r+
z**
------END GEEK CODE BLOCK------

Re: DB code

Christopher A. Watford wrote:

> Prepared query handlers do the correct quotations for you, if they
> don't then it should not be called a prepared query. Prepared queries
> to type checking, cache the base query, and other goodies along with
> proper escaping/quoting. This is why you would use prepared queries,
> so you don't have to worry about escaping user input for fear of
> injection exploits.

Note that currently MDB2 only natively supports prepared queries in the 
oci8, ibase and mysqli driver. I am planning on adding native prepared 
query support for the pgsql driver eventually.

For all other drivers its emulated, including proper quoting of course.

As for caching prepared statements this is a tricky topic. PHP obviously 
has to rely on the database to do this properly for now and for example 
with pgsql you run into issues, because pgsql expects the middleware to 
keep the handle to the prepared statement.

regards,
Lukas

Re: DB code

On 10/17/05, Lukas Kahwe Smith <mls <at> pooteeweet.org> wrote:
> Note that currently MDB2 only natively supports prepared queries in the
> oci8, ibase and mysqli driver. I am planning on adding native prepared
> query support for the pgsql driver eventually.
>
> For all other drivers its emulated, including proper quoting of course.

This is expected behavior of course ;D

> As for caching prepared statements this is a tricky topic. PHP obviously
> has to rely on the database to do this properly for now and for example
> with pgsql you run into issues, because pgsql expects the middleware to
> keep the handle to the prepared statement.

Caching is NOT on the driver end but on the server end. For the length
of the connection, and possibly longer, prepared queries are given a
unique ID and can be held in a planned state until they get cleared.
It changes db to db as to how long or if they are even cached. The
driver MAY do this, but it is not the responsibility of the driver.

> regards,
> Lukas
>

BTW MDB2 is pretty sweet looking.

--
Christopher A. Watford
christopher.watford <at> gmail.com

Re: Hello

Hi Jeremy,

All contacts are read from the DB and then written to the client when
opening the compose form. This is done in program/steps/mail/compose.inc
at line 552.

The quick search function is just made client side using the array with
all contacts. If you implement an LDAP connection, this should probably
be replaced by sending requests to the server and get matching
addresses. Therefore I suggest to create a function (PHP) which can
search for addresses in all address books (the local one and all
subscribed LDAP servers). This function can also be used for a search
function within the address book task.

If you have an LDAP connection implemented, I'll take care about the
auto-complete function, if you want.

Thanks!
Thomas

Jeremy Jongsma wrote:
> I'll probably start on LDAP support, starting with a global address
> book.  Is anybody working on that at the moment?  I'm guessing that it
> will require a fair bit of reworking of the address book logic since
> each user would be able to have multiple addressbooks in different
> sources (global LDAP, personal LDAP, and perhaps multiple local ones).
> 
> Can somebody point me to the code that does address completion in the
> compose window?
> 	

Re: DB code

Christopher A. Watford wrote:

> Why anyone would use non lowercase column/field names is beyond me.

I suppose it also caters for using reserved names as column names too, 
column names of date/time or similar seem to catch several folks out

> I think it was added to the spec merely to support MSSQL's "feature"
> which was a relic of Access. I was also under the impression that
> EVERY major DB supported standard backtick quoted table/field names

postgres doesn't seem to like it, when I installed RC over the weekend I 
had to change all the ` in PHP to \" to get " instead

> and we all know they support all lowercase field/table names, so I see
> this as an issue not with prepared queries but with people being too
> db-centric, as you have pointed out.

agreed, but using prepared statements (in the contextr of a web 
front-end) is more about protecting yourself from malicious users 
crafting "naughty" urls ...

Re: DB code

On 10/17/05, Christopher A. Watford <christopher.watford <at> gmail.com> wrote:
> On 10/17/05, garaged <garaged <at> gmail.com> wrote:
> > Do you think is cleaner or easy to understand to do prepared queries
> > vs correct quotation??
>
> It is much cleaner to do (psuedo):
>
> q = "SELECT field1, field2 FROM table1 WHERE fieldX = :? AND fieldY = :?";
> statement = prepare(q);
> bind_outvalue(statement, 0, &field1, SQL_INT);
> bind_outvalue(statement, 1, &field2, SQL_BOOLEAN);
> bind_invalue(statement, 0, &fieldX, SQL_INT);
> bind_invalue(statement, 1, &fieldY, SQL_STRING);
> query(statement);
>
> print field1, field2;
>
> rather than:
>
> if(!is_int(fieldX))
>   error;
>
> if(!is_string(fieldY))
>   error;
>
> q = "SELECT field1, field2 FROM table1 WHERE fieldX = " + fieldX + "
> AND fieldY = " + quote(fieldY);
>
> result = query(q);
> row = get_row(result);

trying to modify skin

Re: DB code

On 10/17/05, garaged <garaged <at> gmail.com> wrote:
> Do you think is cleaner or easy to understand to do prepared queries
> vs correct quotation??

It is much cleaner to do (psuedo):

q = "SELECT field1, field2 FROM table1 WHERE fieldX = :? AND fieldY = :?";
statement = prepare(q);
bind_outvalue(statement, 0, &field1, SQL_INT);
bind_outvalue(statement, 1, &field2, SQL_BOOLEAN);
bind_invalue(statement, 0, &fieldX, SQL_INT);
bind_invalue(statement, 1, &fieldY, SQL_STRING);
query(statement);

print field1, field2;

rather than:

if(!is_int(fieldX))
   error;

if(!is_string(fieldY))
   error;

q = "SELECT field1, field2 FROM table1 WHERE fieldX = " + fieldX + "
AND fieldY = " + quote(fieldY);

result = query(q);
row = get_row(result);
field1 = row[0];
field2 = row[1];

if(!is_int(field1))

> You have to remember exactly the correct sequence of parameters for
> every query. I'm not that good with memory, but I migth be one in a
> million.
>
> Max
>
> --
> -----BEGIN GEEK CODE BLOCK-----
> Version: 3.12
> GS/S d- s: a-29 C++(+++) ULAHI+++ P+ L++>+++ E--- W++ N* o-- K- w++++
> O- M-- V-- PS+ PE Y-- PGP++ t- 5- X+ R tv++ b+ DI+++ D- G++ e++ h+ r+
> z**
> ------END GEEK CODE BLOCK------
>
>

--
Christopher A. Watford
christopher.watford <at> gmail.com
http://dorm.tunkeymicket.com
http://www.theroadtrip2005.com