php-general Digest 5 Oct 2011 07:40:35 -0000 Issue 7505
php-general Digest 5 Oct 2011 07:40:35 - Issue 7505 Topics (messages 315128 through 315150): Re: Secure data management 315128 by: Stuart Dallas 315129 by: Shawn McKenzie 315130 by: Jim Giner 315131 by: Jeremiah Dodds 315132 by: Stuart Dallas 315133 by: Stuart Dallas 315134 by: Mark Kelly 315135 by: Stuart Dallas 315136 by: Tommy Pham 315137 by: Stuart Dallas 315138 by: Tommy Pham 315139 by: Stuart Dallas 315140 by: Tommy Pham 315141 by: Jeremiah Dodds 315142 by: Stuart Dallas 315143 by: Tommy Pham 315144 by: Jeremiah Dodds 315145 by: Stuart Dallas 315146 by: Jeremiah Dodds 315147 by: Stuart Dallas 315148 by: Tommy Pham 315149 by: Jeremiah Dodds 315150 by: Tommy Pham Administrivia: To subscribe to the digest, e-mail: php-general-digest-subscr...@lists.php.net To unsubscribe from the digest, e-mail: php-general-digest-unsubscr...@lists.php.net To post to the list, e-mail: php-gene...@lists.php.net -- ---BeginMessage--- On 4 Oct 2011, at 20:23, Jim Giner wrote: I thought I knew how to do this. I have a form that collects some data fields. My script checks if magic quotes are off and (since they are) executes addslashes on each input field. Then I run a query to INSERT these 'slashed' vars into the database. But when I go to phpadmin on my site the table does not contain any slashes. Where are they going? 1. Why are you using addslashes? 2. MySQL will strip one level of backslashes. -Stuart -- Stuart Dallas 3ft9 Ltd http://3ft9.com/---End Message--- ---BeginMessage--- On 10/04/2011 02:23 PM, Jim Giner wrote: I thought I knew how to do this. I have a form that collects some data fields. My script checks if magic quotes are off and (since they are) executes addslashes on each input field. Then I run a query to INSERT these 'slashed' vars into the database. But when I go to phpadmin on my site the table does not contain any slashes. Where are they going? The slashes escape data just to tell the database that those characters are data. The database doesn't insert the slash, that would be unwanted. Not all databases use the slash as an escape character and for the ones that do you should use the X_real_escape_string(), like mysql_real_escape_string() instead of addslashes() -- Thanks! -Shawn http://www.spidean.com ---End Message--- ---BeginMessage--- Stuart Dallas stu...@3ft9.com wrote in message news:da8b3499-4d11-4053-9834-68b34d030...@3ft9.com... 1. Why are you using addslashes? 2. MySQL will strip one level of backslashes. * I thought you were supposed to do an addslashes to protect your appl from malicious d/e. Did not know that mysql drops the slashes. ---End Message--- ---BeginMessage--- On Tue, Oct 4, 2011 at 2:44 PM, Jim Giner jim.gi...@albanyhandball.com wrote: I thought you were supposed to do an addslashes to protect your appl from malicious d/e. To protect your app from malicious stuff going to SQL queries, you should be using prepared statements, see http://php.net/manual/en/pdo.prepared-statements.php ---End Message--- ---BeginMessage--- On 4 Oct 2011, at 20:30, Shawn McKenzie wrote: On 10/04/2011 02:23 PM, Jim Giner wrote: I thought I knew how to do this. I have a form that collects some data fields. My script checks if magic quotes are off and (since they are) executes addslashes on each input field. Then I run a query to INSERT these 'slashed' vars into the database. But when I go to phpadmin on my site the table does not contain any slashes. Where are they going? The slashes escape data just to tell the database that those characters are data. The database doesn't insert the slash, that would be unwanted. Not all databases use the slash as an escape character and for the ones that do you should use the X_real_escape_string(), like mysql_real_escape_string() instead of addslashes() http://stut.net/2011/09/15/mysql-real-escape-string-is-not-enough/ -Stuart -- Stuart Dallas 3ft9 Ltd http://3ft9.com/---End Message--- ---BeginMessage--- On 4 Oct 2011, at 20:44, Jim Giner wrote: Stuart Dallas stu...@3ft9.com wrote in message news:da8b3499-4d11-4053-9834-68b34d030...@3ft9.com... 1. Why are you using addslashes? 2. MySQL will strip one level of backslashes. * I thought you were supposed to do an addslashes to protect your appl from malicious d/e. Adding slashes to the data is nowhere near enough protection. Jeremiah is right in saying that prepared statements are the best option available at the moment. Did not know that mysql drops the slashes. I recommend that you look further into why you are doing things like that, especially when it's security-related. The more you know about
php-general Digest 5 Oct 2011 21:16:08 -0000 Issue 7506
php-general Digest 5 Oct 2011 21:16:08 - Issue 7506 Topics (messages 315151 through 315151): Re: Secure data management 315151 by: Mark Kelly Administrivia: To subscribe to the digest, e-mail: php-general-digest-subscr...@lists.php.net To unsubscribe from the digest, e-mail: php-general-digest-unsubscr...@lists.php.net To post to the list, e-mail: php-gene...@lists.php.net -- ---BeginMessage--- Hi. On Wednesday 05 Oct 2011 at 00:04 Mark Kelly wrote: I'd be interested in any ideas folk have about these issues, or any others they can envisage with this proposal. Thank you all for joining in here - it's been a fascinating read so far. Mark ---End Message---
Re: [PHP] Re: Secure data management
On Tue, Oct 4, 2011 at 8:01 PM, Jeremiah Dodds jeremiah.do...@gmail.comwrote:
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
Re: [PHP] Re: Secure data management
Hi. On Wednesday 05 Oct 2011 at 00:04 Mark Kelly wrote: I'd be interested in any ideas folk have about these issues, or any others they can envisage with this proposal. Thank you all for joining in here - it's been a fascinating read so far. Mark -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
