Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:27419
Mailing-List: contact internals-help@lists.php.net; run by ezmlm
Received-SPF: error (pb1.pair.com: domain ch2o.info from 212.27.42.30 cause and error)
Date: Sat, 13 Jan 2007 15:54:13 +0100
Message-ID: <20FA9CB4-D166-4E77-89AC-72912232D32C@ch2o.info>
Sender: "Mathieu CARBONNEAUX" <info@ch2o.info>
To: "Brian Moon" <brianm@dealnews.com>
Reply-To: info@ch2o.info
Cc: "internals@lists.php.net" <internals@lists.php.net>
Importance: Normal
User-Agent: Kerio Outlook Connector (6.1.2.569)
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="utf-8"
Subject: RE: [info@ch2o.info] Re: [PHP-DEV] Comments on PHP security
From: info@ch2o.info ("Mathieu CARBONNEAUX")


> A quick google search for 'sql injection prepared statements' f=
ound
> those 2 statements (1 is a security article) very quickly.
> ...And frankly, its not the manual's job to tell you how to
> write secure code.  So, this has to be coming from people.
> 
Yes but a php security portal that reference all information (lin=
k, and or article) on each security subject by the community (com=
munity portal)...

> Are you saying that filling a variable into a string is slower=20=
than
> calling a function with an array to generate a sql statement? =20=
I don't
> think so.  If you know of such a variable binding library in PH=
P, please
> hook me up.  I will start using it tomorrow.
> 

Ok some sgbd optimisation explain...
if you presente different sql strings with different value in con=
dition close, the sql analyser can not be abel to cache the excut=
ion plant in sql area cache... and when your have very heavy load=
ed site that load massively a sgbd with sql request... they make=20=
enormous difference in perfomance... if you bind variable... in t=
hat way because the analyser know where is variante in the reques=
t and can be abel to not store the variante in his prepare statem=
ent cache...

sample:
	select field1 from tablename where field1=3D5;
	select field1 from tablename where field1=3D6;
	select field1 from tablename where field1=3D7;

All this request execution plant are stored in sql cache...
If you have million request a day... the cache are completly enef=
fective...

And with:
	million requests "select field1 from tablename where field1=3D?;=
" 

with different bind value in "?" are store once in execution prep=
are cache...

the execution plan is the sgbd analyse to find the most efficient=
 walk plan through the table index...to find the good index to wa=
lk the table...
and this analyse is very cpu intensive... is why is interessant t=
o cache it...

ok all sgbd not work in the same way of optimisation... some can=20=
be abel to understand that is the same request (and where is vari=
ant)... but a the cost of more cpu...

and all sgbd that that have bind systeme can benefit from binding=
 optimisation... because you help the analyser to understand who=20=
is variant and who not...

and in addintion you have less risk of sql injection...

is more funy no ? :)

> And, as you said, variable binding has only been used in PHP wi=
th
> prepared statements.  Unless you reuse a statement, you loose
> performance every time you prepare a statement.

No beceause moderne sgbd has sql statement cache and reuse the ex=
ecution plant... in condition of using bind variable...
Without bind variable you lose effectively performance...but you=20=
loose perf not in prepare statment (without prepare statement the=
 sql engine make prepare implicitly internaly...) but in the fact=
 your request prepare execution plan are not cached efficiently..=
.

> 
> So, IMO, there is no performance gain either with prepared
> statements/variable binding for normal, one time use queries.
> 

I dont agree with that for the reason explain on my message.

Best regards,
Mathieu