On Tue, Oct 4, 2011 at 8:01 PM, Jeremiah Dodds <jeremiah.do...@gmail.com>wrote:
> On Tue, Oct 4, 2011 at 9:25 PM, Tommy Pham <tommy...@gmail.com> wrote:
> > There would be a difference in performance since the the expression has
> to
> > be reevaluated, including the function FROM_BASE, every time versus one
> time
> > evaluation of prepared statement.
>
> This is true, but it should be pointed out that for a large majority
> of web applications the performance hit given by either prepared
> statements or base64 encoding is going to be miniscule compared to the
> bottlenecks already present at the db-access and network-latency
> layers. Sites that approach needing to actively worry about the
> performance hit from either method are rare, and it's doubtful that
> the solution used would be to remove the tactic, assuming the reasons
> for the approach being used are sound and still present.
>
> >
> >
> >
> >> As for the added complexity, if you have SQL statements all over your
> code
> >> then yes it will add a time overhead, but any codebase of a significant
> size
> >> should be using a centralised API for database access such that changes
> like
> >> this have a very limited scope.
> >>
> >
> > Isn't that one of the major points of OOP? Still, what about new
> > developers, having to remember that additional (and most likely unneeded)
> > complexity, to the project which they would like to build additional
> > modules/plugins for?
> >
>
> The paragraph you're replying to is saying that this shouldn't be a
> pain if your code is well organized. If your codebase is sane, these
> details should be transparent to new developers. If they can't be,
> then new developers get a chance to learn things :P
>
>
My code base, being sane and KIS, wouldn't contain that base64 :P
Anyway, I've spent the last hour or so trying PoC and some metric analysis
but can't seem to get consistent results in execution speed in the MySQL
workbench testing without (restart server before each SQL statement to
prevent use of cache) and with FLUSH+RESET. Would someone, when you have
time, please see if you're getting the same? Here are the codes:
* PHP + HTML
<?php
if (isset($_POST['data']) && !empty($_POST['data'])) {
$data = $_POST['data'];
$data_base64 = base64_encode($data);
echo $data.'<br/>'.$data_base64;
}
?>
<form method="post" enctype="multipart/form-data">
<input type="text" name="data" size="100" />
<input type="submit" value="Encode" />
</form>
* MySQL syntax + BASE64_DECODE [1]
DROP TABLE IF EXISTS test.base64;
CREATE TABLE IF NOT EXISTS test.base64 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
data_ varchar(100) NOT NULL COLLATE utf8_general_ci,
data_base64 varchar(150) NOT NULL COLLATE utf8_general_ci
);
TRUNCATE test.base64;
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
INSERT INTO test.base64 (data_, data_base64) VALUES ('string to encode
2',BASE64_DECODE('c3RyaW5nIHRvIGVuY29kZSAy')); /* 0.046 sec */
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
PREPARE stmt1 FROM 'INSERT INTO test.base64 (data_, data_base64) VALUES (?,
?)';
SET @a = 'string to encode 3';
SET @b = 'string to encode 3';
EXECUTE stmt1 USING @a, @b; /* 0.015 sec */
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
INSERT INTO test.base64 (data_, data_base64) VALUES ('string to
encode','string to encode'); /* 0.015 sec */
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
SELECT COUNT(*) FROM test.base64; /* 3 rows */
INSERT INTO test.base64 (data_, data_base64) VALUES ('string to
encode','string to encode'); DELETE FROM test.base64;'); /* error after
DELETE -> sample SQL injection */
SELECT COUNT(*) FROM test.base64; /* 0 rows */
INSERT INTO test.base64 (data_, data_base64) VALUES ('string to encode\');
DELETE FROM
test.base64;',BASE64_DECODE('c3RyaW5nIHRvIGVuY29kZScpOyBERUxFVEUgRlJPTSB0ZXN0LmJhc2U2NDs='));
SELECT COUNT(*) FROM test.base64; /* 1 row */
-- test SELECT queries against sample data table with 219,061 rows having 4
rows contains word 'pressure'
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
SELECT * FROM test.base64_product_desc WHERE `name` LIKE CONCAT('%',
BASE64_DECODE('cHJlc3N1cmU='), '%'); /* 0.499 sec / 0.000 sec - subsequent
runs w/o FLUSH/RESET are about same time */
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
SELECT * FROM test.base64_product_desc WHERE `name` LIKE '%pressure%'; /*
0.530 sec / 0.000 sec - subsequent runs are received from cached if not
reset */
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
PREPARE stmt1 FROM 'SELECT * FROM test.base64_product_desc WHERE `name` LIKE
"%?%"'; /* "%?%" \'%?%\' '%?%' */
SET @a = 'pressure';
EXECUTE stmt1 USING @a; /* failed to run */
-- FLUSH TABLES; FLUSH PRIVILEGES; RESET QUERY CACHE;
Times recorded are from the initial run for both INSERT and SELECT.
[1] base64.sql attachment from http://bugs.mysql.com/bug.php?id=18861
