On 7/27/2013 9:55 PM, Pierre Jaury wrote:
> Jerry Stuckle <jstucklex(at)attglobal(dot)net> writes:
>
>> On 7/27/2013 4:25 PM, Pierre Jaury wrote:
>>> Denis McMahon <denismfmcmahon(at)gmail(dot)com> writes:
>>>
>>>> On Sat, 27 Jul 2013 11:45:50 +0200, Luuk wrote:
>>>>
>>>> > On 27-07-2013 11:31, The Natural Philosopher wrote:
>>>>
>>>> >> The target is to create and store thumbnail PNG images in a Mysql
>>>> >> table.
>>>>
>>>> > As far as the subject goes, i would say:
>>>> > DO NOT TOUCH binary data.....
>>>> > Simply store it, or not, in your database.....
>>>>
>>>> Is it trying to store binary data as character data, or does it think it
>>>> needs to string escape binary data types?
>>>
>>> Escaping strings is a concept only useful when building request strings
>>> that include user supplied data. It does avoid concatenating strings
>>> whith special/control characters that may interfere with the original
>>> language. There is no need to escape strings if you do not embed
>>> them inside your request string (eg. if you use bound parameters).
>>>
>>
>> Incorrect. ALL strings, whether user-supplied or not, need to be
>> escaped if you don't use bound values.
>
> Well, mostly depends on what is meant by "user-supplied". I realize I
> was not explicit enough, so: escaping strings is useful when crafting
> interpreted code based on values that are not built in the local
> scope, ie. parameters to your crafting process (which I mentionned and
> will keep mentionning as user-supplied data, wether it is obtained from
> a human operator, a file or basically any routine that is external to
> the crafting scope).
>
They are also required when using strings built in the local scope. In
fact, ANY strings should be processed by mysql_real_escape_string() if
not using prepared statements. You never know, for instance, if someone
later doesn't add a string with a single quote in it.
>>> This is true when dealing with SQL (write prepared statements without
>>> including any user supplied data, then bind the parameters), bash
>>> commands that only require one exec (use exec* instead of system), and
>>> pretty much in any case you construct interpreted code at runtime.
>>>
>>
>> Additional overhead, as I indicated before. Additionally, binding
>> numeric values greatly increases the overhead (because they do not
>> need to be escaped when not using bound variables). They just need to
>> be validated as the appropriate numeric values (which needs to be done
>> in either case).
>>
>
> There is, indeed, overhead due to the two-step model (preparation, then
> execution). However, on a theorical basis, except that operational
> overhead, preparing statements mostly have advantages :
>
Theoretical basis is nice. But PRACTICAL basis is usable.
> * only the symbolic statement is sent as text, data can be shipped in a
> proper binary form (which i believe MySQL does as part of a separate
> query protocol),
Which means additional calls to the server.
> * as a consequence, no need to build and encode strings on the client
> side,
No. It just means the strings need to be shipped separately via
individual calls to the server and assembled there. If the server is
busy, this can also add to performance problem.
> * no need to decode those strings on the server side, the server lexer
> may process smaller strings,
No need to decode the strings otherwise, either. And the server lexer
will still have to assemble the entire string before processing, or
process multiple smaller strings. Another performance hit.
> * no need to escape text data at any time,
No, but escaping strings is not that hard. And numeric values do not
require escaping - but will still result in additional calls required to
bind those parameters in a prepared statement.
> * data-independant statement optimizations may be cached.
>
Using non-prepared statements doesn't change this.
> Even if prepared statements sound theorically ideal, they still *do*
> have a couple of conceptual drawbacks:
>
Nothing is "ideal".
> * replicating queries on multiple servers is much more complex (which
> i believe mysql works around by building fully formatted queries sent to
> binary logs),
Replication is not dependent on whether you use prepared statements or not.
> * logging queries suffers the same complexity (requires that data is
> encoded and a full text or binary query built on the server side),
> * obviously many other related issues.
>
It must be encoded and built to process the statement anyway.
> I am not really into benchmarks and I did not write applications big
> enough based on recent MySQL versions to provide some actual feedback, I
> suspect however that MySQL imlementation of prepared statements is still
> sketchy (especially on the logging, replication and caching sides,
> apparently). Maybe performance will improve with future versions of the
> statement engine.
>
Performance can be critical in some cases. It is something a good
programmer ALWAYS takes into consideration when writing code.
> I would really like to read some real-world performance feedback on the
> matter, if you have some interesting links to share :)
>
You can google as well as I can. Or you can run your own benchmarks,
like I have.
>> A little knowledge is a dangerous thing.
>
> I do not feel in a trolling mood, please stop these.
>
No trolling. Just an observation you are obviously aware of only SOME
of the processing involved, and have made incorrect statements based on
that limited knowledge.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
Calendar
Search
Help
Members
Register
Login
Home
]