I need a fresh look at storing variables in MySQL

Hi gang:

I just completed writing a survey that has approximately 180
questions in it and I need a fresh look at how to store the results
so I can use them later.

The survey requires the responder to identify themselves via an
authorization script. After which, the responder is permitted to take
the survey. Everything works as the client wants so there are no
problems there.

My question is how to store the results?

I have the answers stored in a session variable, like:

$_SESSION['answer']['e1']
$_SESSION['answer']['e2']
$_SESSION['answer']['e2a']
$_SESSION['answer']['e2ai']
$_SESSION['answer']['p1']
$_SESSION['answer']['p1a']
$_SESSION['answer']['p1ai']

and so on. As I said, there are around 180 questions/answers.

Most of the answers are integers (less than 100), some are text, and
some will be null.

Each "vote" will have a unique number (i.e., time) assigned to it as
well as a common survey id.

My first thought was to simply record the "vote" as a single record
with the answers as a long string (maybe MEDIUMTEXT), such as:

1, 1268501271, e1, 1, e2, 16, e2a, Four score and ..., e2a1, ,

Then I thought I might make the data xml, such as:

11268501271116Four
score and ...

That way I can strip text entries for <> and have absolute control
over question separation.

Then I thought I could make each question/answer combination have
it's own record while using the vote_id to tie the "vote" together.
That way I can use MySQL to do the heavy lifting during the analysis.
While each "vote" creates 180 records, I like this way best.

Then I thought, what would you guys do? So, what would you guys do?

Keep in mind that this survey must evaluated in terms of answers,
such as "Of the ones who answered e1 as 1 how did they answer e2?"

If there is something wrong with my preference, please let me know.

Thanks,

tedd

--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: I need a fresh look at storing variables in MySQL

--00c09f8e5f6a2dd8fc0481b330cd
Content-Type: text/plain; charset=ISO-8859-1

On Sun, Mar 14, 2010 at 2:10 AM, tedd wrote:

> Hi gang:
>
> I just completed writing a survey that has approximately 180 questions in
> it and I need a fresh look at how to store the results so I can use them
> later.
>
> The survey requires the responder to identify themselves via an
> authorization script. After which, the responder is permitted to take the
> survey. Everything works as the client wants so there are no problems there.
>
> My question is how to store the results?
>
> I have the answers stored in a session variable, like:
>
> $_SESSION['answer']['e1']
> $_SESSION['answer']['e2']
> $_SESSION['answer']['e2a']
> $_SESSION['answer']['e2ai']
> $_SESSION['answer']['p1']
> $_SESSION['answer']['p1a']
> $_SESSION['answer']['p1ai']
>
> and so on. As I said, there are around 180 questions/answers.
>
> Most of the answers are integers (less than 100), some are text, and some
> will be null.
>
> Each "vote" will have a unique number (i.e., time) assigned to it as well
> as a common survey id.
>
> My first thought was to simply record the "vote" as a single record with
> the answers as a long string (maybe MEDIUMTEXT), such as:
>
> 1, 1268501271, e1, 1, e2, 16, e2a, Four score and ..., e2a1, ,
>
> Then I thought I might make the data xml, such as:
>
> 11268501271116Four
> score and ...
>
> That way I can strip text entries for <> and have absolute control over
> question separation.
>
> Then I thought I could make each question/answer combination have it's own
> record while using the vote_id to tie the "vote" together. That way I can
> use MySQL to do the heavy lifting during the analysis. While each "vote"
> creates 180 records, I like this way best.
>
> Then I thought, what would you guys do? So, what would you guys do?
>
> Keep in mind that this survey must evaluated in terms of answers, such as
> "Of the ones who answered e1 as 1 how did they answer e2?"
>
> If there is something wrong with my preference, please let me know.
>
> Thanks,
>
> tedd
>
> --
> -------
> http://sperling.com http://ancientstones.com http://earthstones.com
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
Hi Ted,

How about saving the array in serialize variable to TEXT column

$answer = serialize($_SESSION['answer']);

to get the original array value simply unserialize the value from the
database..

Paragasu

--00c09f8e5f6a2dd8fc0481b330cd--

Re: I need a fresh look at storing variables in MySQL

+1, but it also kinda depends on what you want to do with it later,
and how much.

if you want to re-use filled-in reports on the javascript end, you may
want to use json_encode() instead of serialize().

if you plan to have many (say >5000) reports filled in and want users
to be able to search quickly on values or generate reports based on
searches, then you'd need an actual datamodel, so mysql can do the
searching, sorting and adding.

On Sat, Mar 13, 2010 at 7:55 PM, paragasu wrote:
> On Sun, Mar 14, 2010 at 2:10 AM, tedd wrote:
>
>> Hi gang:
>>
>> I just completed writing a survey that has approximately 180 questions i=
n
>> it and I need a fresh look at how to store the results so I can use them
>> later.
>>
>> The survey requires the responder to identify themselves via an
>> authorization script. After which, the responder is permitted to take th=
e
>> survey. Everything works as the client wants so there are no problems th=
ere.
>>
>> My question is how to store the results?
>>
>> I have the answers stored in a session variable, like:
>>
>> $_SESSION['answer']['e1']
>> $_SESSION['answer']['e2']
>> $_SESSION['answer']['e2a']
>> $_SESSION['answer']['e2ai']
>> $_SESSION['answer']['p1']
>> $_SESSION['answer']['p1a']
>> $_SESSION['answer']['p1ai']
>>
>> and so on. As I said, there are around 180 questions/answers.
>>
>> Most of the answers are integers (less than 100), some are text, and som=
e
>> will be null.
>>
>> Each "vote" will have a unique number (i.e., time) assigned to it as wel=
l
>> as a common survey id.
>>
>> My first thought was to simply record the "vote" as a single record with
>> the answers as a long string (maybe MEDIUMTEXT), such as:
>>
>> 1, 1268501271, e1, 1, e2, 16, e2a, Four score and ..., e2a1, ,
>>
>> Then I thought I might make the data xml, such as:
>>
>> 11268501271116 2>Four
>> score and ...
>>
>> That way I can strip text entries for <> and have absolute control over
>> question separation.
>>
>> Then I thought I could make each question/answer combination have it's o=
wn
>> record while using the vote_id to tie the "vote" together. That way I ca=
n
>> use MySQL to do the heavy lifting during the analysis. While each "vote"
>> creates 180 records, I like this way best.
>>
>> Then I thought, what would you guys do? So, what would you guys do?
>>
>> Keep in mind that this survey must evaluated in terms of answers, such a=
s
>> "Of the ones who answered e1 as 1 how did they answer e2?"
>>
>> If there is something wrong with my preference, please let me know.
>>
>> Thanks,
>>
>> tedd
>>
>> --
>> -------
>> http://sperling.com =A0http://ancientstones.com =A0http://earthstones.co=
m
>>
>> --
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
> Hi Ted,
>
> How about saving the array in serialize variable to TEXT column
>
> $answer =3D serialize($_SESSION['answer']);
>
> to get the original array value simply unserialize the value from the
> database..
>
> Paragasu
>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: I need a fresh look at storing variables in MySQL

I'd go with a mysql data modelled approach as it will allow mysql to
do lots of the heavy lifting during analysis as you've mentioned. If
there are a lot of entries, it's gonna get complex and expensive
memory-wise to manage XML or session based datasets.

Plus having each question as it's own record should give you greater
flexibility in packaging the data for analysis and reporting.

Bastien

Sent from my iPod

On Mar 13, 2010, at 1:10 PM, tedd wrote:

> Hi gang:
>
> I just completed writing a survey that has approximately 180
> questions in it and I need a fresh look at how to store the results
> so I can use them later.
>
> The survey requires the responder to identify themselves via an
> authorization script. After which, the responder is permitted to
> take the survey. Everything works as the client wants so there are
> no problems there.
>
> My question is how to store the results?
>
> I have the answers stored in a session variable, like:
>
> $_SESSION['answer']['e1']
> $_SESSION['answer']['e2']
> $_SESSION['answer']['e2a']
> $_SESSION['answer']['e2ai']
> $_SESSION['answer']['p1']
> $_SESSION['answer']['p1a']
> $_SESSION['answer']['p1ai']
>
> and so on. As I said, there are around 180 questions/answers.
>
> Most of the answers are integers (less than 100), some are text, and
> some will be null.
>
> Each "vote" will have a unique number (i.e., time) assigned to it as
> well as a common survey id.
>
> My first thought was to simply record the "vote" as a single record
> with the answers as a long string (maybe MEDIUMTEXT), such as:
>
> 1, 1268501271, e1, 1, e2, 16, e2a, Four score and ..., e2a1, ,
>
> Then I thought I might make the data xml, such as:
>
> 112685012711 > e1>16Four score and ...
>
> That way I can strip text entries for <> and have absolute control
> over question separation.
>
> Then I thought I could make each question/answer combination have
> it's own record while using the vote_id to tie the "vote" together.
> That way I can use MySQL to do the heavy lifting during the
> analysis. While each "vote" creates 180 records, I like this way best.
>
> Then I thought, what would you guys do? So, what would you guys do?
>
> Keep in mind that this survey must evaluated in terms of answers,
> such as "Of the ones who answered e1 as 1 how did they answer e2?"
>
> If there is something wrong with my preference, please let me know.
>
> Thanks,
>
> tedd
>
> --
> -------
> http://sperling.com http://ancientstones.com http://earthstones.com
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: I need a fresh look at storing variables in MySQL

the OP may not need such fanciness.. it depends on the amount of
reports he wants to store, the types of searches (if any) done by
browsers, and the amount of searches to expect.

and the OP may not have good db design skills yet.
for a noob, it's one timeconsuming thing to build a datamodel, but
it's harder to get it efficient yet simple.

if the oop wants to start a site with lots of reports and lots of
searching, we'd even have to remind him of the cloud hosting option,
which i'm sorry to say, has no real rdbms (mysql) support yet, and is
not always paid by the minute (but by the hour), meaning it's got only
a small chance of reducing his operating cost (by a lot); it's still
worth investigating in this case (given the much easier scaling
abilities of cloud hosting).

OP: if you need a mysql datamodel for reports, i'm willing to give it
a free shot. i'm sure others here would too, or improve upon mine.
It's probably not more than 3 tables i think.
Let us know eh..

On Sat, Mar 13, 2010 at 8:45 PM, Phpster wrote:
> I'd go with a mysql data modelled approach as it will allow mysql to do l=
ots
> of the heavy lifting during analysis as you've mentioned. If there are a =
lot
> of entries, it's gonna get complex and expensive memory-wise to manage XM=
L
> or session based datasets.
>
> Plus having each question as it's own record should give you greater
> flexibility in packaging the data for analysis and reporting.
>
> Bastien
>
> Sent from my iPod
>
> On Mar 13, 2010, at 1:10 PM, tedd wrote:
>
>> Hi gang:
>>
>> I just completed writing a survey that has approximately 180 questions i=
n
>> it and I need a fresh look at how to store the results so I can use them
>> later.
>>
>> The survey requires the responder to identify themselves via an
>> authorization script. After which, the responder is permitted to take th=
e
>> survey. Everything works as the client wants so there are no problems th=
ere.
>>
>> My question is how to store the results?
>>
>> I have the answers stored in a session variable, like:
>>
>> $_SESSION['answer']['e1']
>> $_SESSION['answer']['e2']
>> $_SESSION['answer']['e2a']
>> $_SESSION['answer']['e2ai']
>> $_SESSION['answer']['p1']
>> $_SESSION['answer']['p1a']
>> $_SESSION['answer']['p1ai']
>>
>> and so on. As I said, there are around 180 questions/answers.
>>
>> Most of the answers are integers (less than 100), some are text, and som=
e
>> will be null.
>>
>> Each "vote" will have a unique number (i.e., time) assigned to it as wel=
l
>> as a common survey id.
>>
>> My first thought was to simply record the "vote" as a single record with
>> the answers as a long string (maybe MEDIUMTEXT), such as:
>>
>> 1, 1268501271, e1, 1, e2, 16, e2a, Four score and ..., e2a1, ,
>>
>> Then I thought I might make the data xml, such as:
>>
>>
>> 11268501271116 2>Four
>> score and ...
>>
>> That way I can strip text entries for <> and have absolute control over
>> question separation.
>>
>> Then I thought I could make each question/answer combination have it's o=
wn
>> record while using the vote_id to tie the "vote" together. That way I ca=
n
>> use MySQL to do the heavy lifting during the analysis. While each "vote"
>> creates 180 records, I like this way best.
>>
>> Then I thought, what would you guys do? So, what would you guys do?
>>
>> Keep in mind that this survey must evaluated in terms of answers, such a=
s
>> "Of the ones who answered e1 as 1 how did they answer e2?"
>>
>> If there is something wrong with my preference, please let me know.
>>
>> Thanks,
>>
>> tedd
>>
>> --
>> -------
>> http://sperling.com =A0http://ancientstones.com =A0http://earthstones.co=
m
>>
>> --
>> PHP General Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: I need a fresh look at storing variables in MySQL

On Sat, Mar 13, 2010 at 02:45:37PM -0500, Phpster wrote:

> I'd go with a mysql data modelled approach as it will allow mysql to
> do lots of the heavy lifting during analysis as you've mentioned. If
> there are a lot of entries, it's gonna get complex and expensive
> memory-wise to manage XML or session based datasets.
>
> Plus having each question as it's own record should give you greater
> flexibility in packaging the data for analysis and reporting.
>

+1

I invariably find that the original design for a project needs to be
tweaked. The customer wants to add or delete questions, they want to
add/change reports for the data. So I nearly always approach this kind
of project this way. Maybe:

vote table:

id serial/sequence not null primary key
voter_id int references voters (voter_id)
question_id varchar(10)
answer varchar(10)

You can easily subset by voter, or by question ID. Or analyze answers in
relation to other answers, etc.

Paul

--
Paul M. Foster

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: I need a fresh look at storing variables in MySQL

On Sat, Mar 13, 2010 at 09:04:46PM +0100, Rene Veerman wrote:



>
> and the OP may not have good db design skills yet.
> for a noob, it's one timeconsuming thing to build a datamodel, but
> it's harder to get it efficient yet simple.
>



>
> OP: if you need a mysql datamodel for reports, i'm willing to give it
> a free shot. i'm sure others here would too, or improve upon mine.
> It's probably not more than 3 tables i think.
> Let us know eh..

Tedd's perfectly capable of speaking for himself, but I can tell you
he's been on this list for a long time, and his skills are plenty
adequate for this task. He's just asking for second opinions.

Paul

--
Paul M. Foster

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: I need a fresh look at storing variables in MySQL

Hi Tedd,

just a few thoughts that might help ...

Op 3/13/10 6:10 PM, tedd schreef:
> Hi gang:
>
> I just completed writing a survey that has approximately 180 questions
> in it and I need a fresh look at how to store the results so I can use
> them later.

first off - wasn't there a cut'n'dried piece of survey software out there
that did the job? don't know off hand what the 'market' currently offers but
I'm pretty sure there are a number of candidate php-based wotsits.

as such they might be worth looking at just to check out their data models.

> The survey requires the responder to identify themselves via an
> authorization script. After which, the responder is permitted to take
> the survey. Everything works as the client wants so there are no
> problems there.
>
> My question is how to store the results?
>
> I have the answers stored in a session variable, like:
>
> $_SESSION['answer']['e1']
> $_SESSION['answer']['e2']
> $_SESSION['answer']['e2a']
> $_SESSION['answer']['e2ai']
> $_SESSION['answer']['p1']
> $_SESSION['answer']['p1a']
> $_SESSION['answer']['p1ai']
>
> and so on. As I said, there are around 180 questions/answers.
>
> Most of the answers are integers (less than 100), some are text, and
> some will be null.
>
> Each "vote" will have a unique number (i.e., time) assigned to it as
> well as a common survey id.

what happens when 2 people vote at the same time?

>
> My first thought was to simply record the "vote" as a single record with
> the answers as a long string (maybe MEDIUMTEXT), such as:
>
> 1, 1268501271, e1, 1, e2, 16, e2a, Four score and ..., e2a1, ,

that would make life very difficult if you wanted to use the

> Then I thought I might make the data xml, such as:
>
> 11268501271116Four
> score and ...

doesn't seem like XML is the answer at all. isn't it Larry Garfield with the
sig line that says:

Sometime a programmer has a problem and thinks "I know I'll use XML",
now he has 2 problems.

:)

> That way I can strip text entries for <> and have absolute control over
> question separation.
>
> Then I thought I could make each question/answer combination have it's
> own record while using the vote_id to tie the "vote" together. That way
> I can use MySQL to do the heavy lifting during the analysis. While each
> "vote" creates 180 records, I like this way best.

is there only ever going to be one survey of which the questions/structure
is now fixed/definitive?

if so I'd probably opt for the simple approach of a table
with 180 columns purely because that would make for the easiest
reporting queries (no self-referencing joins needed to answer the
question posed below ... which would be the case if you normalized
the data to one row per question+answer+vote[r])

.... although possibly not speediest in terms of SQL performance
(you'd have to be careful with creating lots of indexes because that
would affect insert performance)

basically one table with 180 answer columns and an addition primary [voter?] key,
possibly also a survey id if your going to be repeating the survey over time.

a more normalized approach would be to define all the questions and their
answer types in one table, surveys in another, with answers per qestion in another:

survey table:
id INT (PK)
name VARCHAR
date TIMESTAMP

questions table:
id INT (PK)
position INT - order of questions
survey_id INT
question VARCHAR/TEXT
question_type ENUM?

voters table:
id INT (PK)
name VARCHAR ??

answers tables:
id INT (PK)
voter_id INT
question_id INT
answer ?

with the answer values in the answers table you might consider a field for
each question_type you define so that you can use a proper data type - this
would be somewhat denormalized because you'd only ever use one of those fields
per row but it might come in handy.



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: I need a fresh look at storing variables in MySQL

On Sun, Mar 14, 2010 at 7:18 AM, Paul M Foster wrote:
>
> Tedd's perfectly capable of speaking for himself, but I can tell you
> he's been on this list for a long time, and his skills are plenty
> adequate for this task. He's just asking for second opinions.
>
Wouldn't someone with adequate DB skills know if he(/she) even needs
to build a datamodel, and given the simplicity of this one, how? Based
on what i mentioned earlier, type and amount of use of stored reports?

I don't mind noobishness in any area, but i have learned to keep code
as simple as possible.

BTW;
- as always, i recommend adodb.sf.net for DB abstractions.
- if you are storing in DB and displaying from DB later you need to
prevent code injections (sql, html, js, flash) by pushing all strings
used in sql insert- and update-fields;
$sql = 'insert into table (field1_int, field2_string,etc) values
('.$field1.', "'.antiSQLinjection($field2).'", ...);

I'm using this function atm, maybe someone can improve upon it. This
disables all sql injections, and strips all html, js & flash.

function antiSQLinjection ($string) {

//anti SQL injections:
if (phpversion() >= '4.3.0')
{
$string = mysql_real_escape_string($string);
}
else
{
$string = mysql_escape_string($string);
}

if(get_magic_quotes_gpc()) // prevents duplicate backslashes
{
$string = stripslashes($string);
}

//anti HTML/JS/flash injections (into searchterms, for instance):
$string = strip_tags ($string);

return $string;
}

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: I need a fresh look at storing variables in MySQL

--=-Q6p7PZi1PevsWFRlijlJ
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Sun, 2010-03-14 at 11:15 +0100, Rene Veerman wrote:

> On Sun, Mar 14, 2010 at 7:18 AM, Paul M Foster wrote:
> >
> > Tedd's perfectly capable of speaking for himself, but I can tell you
> > he's been on this list for a long time, and his skills are plenty
> > adequate for this task. He's just asking for second opinions.
> >
> Wouldn't someone with adequate DB skills know if he(/she) even needs
> to build a datamodel, and given the simplicity of this one, how? Based
> on what i mentioned earlier, type and amount of use of stored reports?
>
> I don't mind noobishness in any area, but i have learned to keep code
> as simple as possible.
>
> BTW;
> - as always, i recommend adodb.sf.net for DB abstractions.
> - if you are storing in DB and displaying from DB later you need to
> prevent code injections (sql, html, js, flash) by pushing all strings
> used in sql insert- and update-fields;
> $sql = 'insert into table (field1_int, field2_string,etc) values
> ('.$field1.', "'.antiSQLinjection($field2).'", ...);
>
> I'm using this function atm, maybe someone can improve upon it. This
> disables all sql injections, and strips all html, js & flash.
>
> function antiSQLinjection ($string) {
>
> //anti SQL injections:
> if (phpversion() >= '4.3.0')
> {
> $string = mysql_real_escape_string($string);
> }
> else
> {
> $string = mysql_escape_string($string);
> }
>
> if(get_magic_quotes_gpc()) // prevents duplicate backslashes
> {
> $string = stripslashes($string);
> }
>
> //anti HTML/JS/flash injections (into searchterms, for instance):
> $string = strip_tags ($string);
>
> return $string;
> }
>


That function won't always work. You're using a PHP version check for
mysql_real_escape_string() when the most likely failure point for it is
if no database connection has been opened.

Also, you shouldn't strip the tags from a string that's being inserted
into the database. strip_tags() is for the display of data on a web
page. It's best practice not to alter the actual data you've stored but
to convert it once it's displayed. Don't forget that the browser display
may not be the only use for that data.

Thanks,
Ash
http://www.ashleysheridan.co.uk



--=-Q6p7PZi1PevsWFRlijlJ--

Re: I need a fresh look at storing variables in MySQL

On Sun, Mar 14, 2010 at 8:22 AM, Jochem Maas wrote:
>
> first off - wasn't there a cut'n'dried piece of survey software out there
> that did the job? don't know off hand what the 'market' currently offers but
> I'm pretty sure there are a number of candidate php-based wotsits.
>
> as such they might be worth looking at just to check out their data models.
>

+1, good point.

I know there are free cloud services for dutch petitions and surveys,
i bet there are for english too.
A google for "free online survey hosting" will reap many such sites.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: I need a fresh look at storing variables in MySQL

--=-Z8XzekBaEvpAcTDYUWct
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Sun, 2010-03-14 at 12:14 +0100, Rene Veerman wrote:

>
>
>
> On Sun, Mar 14, 2010 at 11:16 AM, Ashley Sheridan
> wrote:
>
>
>
>
> That function won't always work. You're using a PHP version
> check for mysql_real_escape_string() when the most likely
> failure point for it is if no database connection has been
> opened.
>
>
> I never call it without an open db connection..
>
>
> Also, you shouldn't strip the tags from a string that's being
> inserted into the database. strip_tags() is for the display of
> data on a web page. It's best practice not to alter the actual
> data you've stored but to convert it once it's displayed.
> Don't forget that the browser display may not be the only use
> for that data.
>
>
>
> Let's call that a coder's / payer's preference..
>
> If i'd need human text, i'd want to strip it of computer code before
> it enters the db. Possibly log the attempt to insert code.
>
>
>
>


I have to deal with a lot of CMS's, so I expect the users to enter some
HTML code through a rich-text editor, and they expect to be able to.

Aside from that, it's good to have a complete copy of the code a user
attempted to insert, to see the methodology of an attack should it ever
occur.

Thanks,
Ash
http://www.ashleysheridan.co.uk



--=-Z8XzekBaEvpAcTDYUWct--

Re: I need a fresh look at storing variables in MySQL

--00163649954b2edf7b0481c0de0c
Content-Type: text/plain; charset=ISO-8859-1

On Sun, Mar 14, 2010 at 11:16 AM, Ashley Sheridan
wrote:

> That function won't always work. You're using a PHP version check for
> mysql_real_escape_string() when the most likely failure point for it is if
> no database connection has been opened.
>

I never call it without an open db connection..


>
> Also, you shouldn't strip the tags from a string that's being inserted into
> the database. strip_tags() is for the display of data on a web page. It's
> best practice not to alter the actual data you've stored but to convert it
> once it's displayed. Don't forget that the browser display may not be the
> only use for that data.
>

Let's call that a coder's / payer's preference..

If i'd need human text, i'd want to strip it of computer code before it
enters the db. Possibly log the attempt to insert code.

--00163649954b2edf7b0481c0de0c--

Re: I need a fresh look at storing variables in MySQL

--0015174c15c4c3b6290481c101d2
Content-Type: text/plain; charset=ISO-8859-1

On Sun, Mar 14, 2010 at 12:13 PM, Ashley Sheridan
wrote:

>
> I have to deal with a lot of CMS's, so I expect the users to enter some
> HTML code through a rich-text editor, and they expect to be able to.
>

I'd love to have a copy of whatever function you use to filter out bad
HTML/js/flash for use cases where users are allowed to enter html.
I'm aware of strip_tags() "allowed tags" param, but haven't got a good list
for it.


>
> Aside from that, it's good to have a complete copy of the code a user
> attempted to insert, to see the methodology of an attack should it ever
> occur.
>

I should've said "possibly log & mail the details of the attempt", which is
what i'd do ;)

--0015174c15c4c3b6290481c101d2--

Re: I need a fresh look at storing variables in MySQL

On Sun, Mar 14, 2010 at 12:24 PM, Rene Veerman wrote:
>
> I'd love to have a copy of whatever function you use to filter out bad
> HTML/js/flash for use cases where users are allowed to enter html.
> I'm aware of strip_tags() "allowed tags" param, but haven't got a good list
> for it.
>

oh, and even tags can be used for cookie-stuffing on many browsers..

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: I need a fresh look at storing variables in MySQL

--=-Zc67TuKVZ0zQcZ1F90kz
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Sun, 2010-03-14 at 12:25 +0100, Rene Veerman wrote:

> On Sun, Mar 14, 2010 at 12:24 PM, Rene Veerman wrote:
> >
> > I'd love to have a copy of whatever function you use to filter out bad
> > HTML/js/flash for use cases where users are allowed to enter html.
> > I'm aware of strip_tags() "allowed tags" param, but haven't got a good list
> > for it.
> >
>
> oh, and even tags can be used for cookie-stuffing on many browsers..
>


Yes, and you call strip_tags() before the data goes to the browser for
display, not before it gets inserted into the database. Essentially, you
need to keep as much original information as possible.

Thanks,
Ash
http://www.ashleysheridan.co.uk



--=-Zc67TuKVZ0zQcZ1F90kz--

Re: I need a fresh look at storing variables in MySQL

Op 3/14/10 11:45 AM, Ashley Sheridan schreef:
> On Sun, 2010-03-14 at 12:25 +0100, Rene Veerman wrote:
>
>> On Sun, Mar 14, 2010 at 12:24 PM, Rene Veerman wrote:
>>>
>>> I'd love to have a copy of whatever function you use to filter out bad
>>> HTML/js/flash for use cases where users are allowed to enter html.
>>> I'm aware of strip_tags() "allowed tags" param, but haven't got a good list
>>> for it.
>>>
>>
>> oh, and even tags can be used for cookie-stuffing on many browsers..
>>
>
>
> Yes, and you call strip_tags() before the data goes to the browser for
> display, not before it gets inserted into the database. Essentially, you
> need to keep as much original information as possible.

I disagree with both you. I'm like that :)

let's assume we're not talking about data that is allowed to contain HTML,
in such cases I would do a strip_tags() on the incoming data then compare
the output ofstrip_tags() to the original input ... if they don't match then
I would log the problem and refuse to input the data at all.

using strip_tags() on a piece of data everytime you output it if you know
that it shouldn't contain any in the first is a waste of resources ... this
does assume that you can trust the data source ... which in the case of a database
that you control should be the case.

at any rate, strip_tags() doesn't belong in an 'anti-sql-injection' routine as
it has nothing to do with sql injection at all.

>
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk
>
>
>


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: I need a fresh look at storing variables in MySQL

'Twas brillig, and Jochem Maas at 14/03/10 23:56 did gyre and gimble:
> Op 3/14/10 11:45 AM, Ashley Sheridan schreef:
>> On Sun, 2010-03-14 at 12:25 +0100, Rene Veerman wrote:
>>
>>> On Sun, Mar 14, 2010 at 12:24 PM, Rene Veerman wrote:
>>>>
>>>> I'd love to have a copy of whatever function you use to filter out bad
>>>> HTML/js/flash for use cases where users are allowed to enter html.
>>>> I'm aware of strip_tags() "allowed tags" param, but haven't got a good list
>>>> for it.
>>>>
>>>
>>> oh, and even tags can be used for cookie-stuffing on many browsers..
>>>
>>
>>
>> Yes, and you call strip_tags() before the data goes to the browser for
>> display, not before it gets inserted into the database. Essentially, you
>> need to keep as much original information as possible.
>
> I disagree with both you. I'm like that :)
>
> let's assume we're not talking about data that is allowed to contain HTML,
> in such cases I would do a strip_tags() on the incoming data then compare
> the output ofstrip_tags() to the original input ... if they don't match then
> I would log the problem and refuse to input the data at all.
>
> using strip_tags() on a piece of data everytime you output it if you know
> that it shouldn't contain any in the first is a waste of resources ... this
> does assume that you can trust the data source ... which in the case of a database
> that you control should be the case.

I used to think like that too, but I've relatively recently changed my
position.

While it's not as extreme an example, I used to keep data in the
database *after* I had processed it with htmlspecialchars() (not quite
the same as strip_tags, but the principle is the same).

The issue I had was that over time, I've found the need to output to
other formats - e.g. spread sheets, plain text emails, PDFs etc. in
which case this pre-encoded format is a pain and I have to call
html_entity_decode() to reverse the htmlspecialchars() I did in the
first place. This is a royal pain in the bum and it's really ugly in the
code, remembering what format the data is in in order to process it
appropriately at the right points.

Nowadays I work rather differently and always escape at the point of
output (this does not exclude filtering at the point of input too, but I
do not keep things encoded any longer - I keep it raw).

Any half way decently designed caching layer will prevent any major
impact from escaping at the point of output anyway.

Now you could argue that encoding at the save point and reversing the
encoding when needed is still a better approach and I wont argue too
heavily, but for the sake of my sanity I'm much happier working the way
I do now. The view layers are very clearly escaping everything that
needs escaping and no logic for the "is it or is it not already escaped"
leaks into this layer.

(I appreciate strip tags and htmlspecialchars are not the same and my
general usage may not apply to a pure striptags usage).

> at any rate, strip_tags() doesn't belong in an 'anti-sql-injection' routine as
> it has nothing to do with sql injection at all.

Indeed, it's more about XSS and CSRF rather than SQL injection.

Col

--

Colin Guthrie
gmane(at)colin.guthr.ie
http://colin.guthr.ie/

Day Job:
Tribalogic Limited [http://www.tribalogic.net/]
Open Source:
Mandriva Linux Contributor [http://www.mandriva.com/]
PulseAudio Hacker [http://www.pulseaudio.org/]
Trac Hacker [http://trac.edgewall.org/]


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Re: I need a fresh look at storing variables in MySQL

--=-J1tLXgWw8UeRPghsIpk1
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Mon, 2010-03-15 at 12:48 +0000, Colin Guthrie wrote:

> 'Twas brillig, and Jochem Maas at 14/03/10 23:56 did gyre and gimble:
> > Op 3/14/10 11:45 AM, Ashley Sheridan schreef:
> >> On Sun, 2010-03-14 at 12:25 +0100, Rene Veerman wrote:
> >>
> >>> On Sun, Mar 14, 2010 at 12:24 PM, Rene Veerman wrote:
> >>>>
> >>>> I'd love to have a copy of whatever function you use to filter out bad
> >>>> HTML/js/flash for use cases where users are allowed to enter html.
> >>>> I'm aware of strip_tags() "allowed tags" param, but haven't got a good list
> >>>> for it.
> >>>>
> >>>
> >>> oh, and even tags can be used for cookie-stuffing on many browsers..
> >>>
> >>
> >>
> >> Yes, and you call strip_tags() before the data goes to the browser for
> >> display, not before it gets inserted into the database. Essentially, you
> >> need to keep as much original information as possible.
> >
> > I disagree with both you. I'm like that :)
> >
> > let's assume we're not talking about data that is allowed to contain HTML,
> > in such cases I would do a strip_tags() on the incoming data then compare
> > the output ofstrip_tags() to the original input ... if they don't match then
> > I would log the problem and refuse to input the data at all.
> >
> > using strip_tags() on a piece of data everytime you output it if you know
> > that it shouldn't contain any in the first is a waste of resources ... this
> > does assume that you can trust the data source ... which in the case of a database
> > that you control should be the case.
>
> I used to think like that too, but I've relatively recently changed my
> position.
>
> While it's not as extreme an example, I used to keep data in the
> database *after* I had processed it with htmlspecialchars() (not quite
> the same as strip_tags, but the principle is the same).
>
> The issue I had was that over time, I've found the need to output to
> other formats - e.g. spread sheets, plain text emails, PDFs etc. in
> which case this pre-encoded format is a pain and I have to call
> html_entity_decode() to reverse the htmlspecialchars() I did in the
> first place. This is a royal pain in the bum and it's really ugly in the
> code, remembering what format the data is in in order to process it
> appropriately at the right points.
>
> Nowadays I work rather differently and always escape at the point of
> output (this does not exclude filtering at the point of input too, but I
> do not keep things encoded any longer - I keep it raw).
>
> Any half way decently designed caching layer will prevent any major
> impact from escaping at the point of output anyway.
>
> Now you could argue that encoding at the save point and reversing the
> encoding when needed is still a better approach and I wont argue too
> heavily, but for the sake of my sanity I'm much happier working the way
> I do now. The view layers are very clearly escaping everything that
> needs escaping and no logic for the "is it or is it not already escaped"
> leaks into this layer.
>
> (I appreciate strip tags and htmlspecialchars are not the same and my
> general usage may not apply to a pure striptags usage).
>
> > at any rate, strip_tags() doesn't belong in an 'anti-sql-injection' routine as
> > it has nothing to do with sql injection at all.
>
> Indeed, it's more about XSS and CSRF rather than SQL injection.
>
> Col
>
> --
>
> Colin Guthrie
> gmane(at)colin.guthr.ie
> http://colin.guthr.ie/
>
> Day Job:
> Tribalogic Limited [http://www.tribalogic.net/]
> Open Source:
> Mandriva Linux Contributor [http://www.mandriva.com/]
> PulseAudio Hacker [http://www.pulseaudio.org/]
> Trac Hacker [http://trac.edgewall.org/]
>
>


You could escape the content with strip_tags() and insert both copies
into the database if you're really worried about wasted resources. That
way, you keep a copy of the original data, and the one you're most
likely going to display in a web page.

It's like the whole argument about modifying textarea content to replace
newlines with
tags. At some point, you might need that content for
another use, and when you do, you'll wish you had the original. Just
because you don't see that use in your immediate future, it doesn't mean
it won't occur.

Thanks,
Ash
http://www.ashleysheridan.co.uk



--=-J1tLXgWw8UeRPghsIpk1--

Re: fresh look at storing variables in MySQL [Solution]

At 1:10 PM -0500 3/13/10, tedd wrote:
>I just completed writing a survey that has approximately 180
>questions in it and I need a fresh look at how to store the results
>so I can use them later.

I decided to store all questions/answers pairs in MySQL as individual records.

Now I could have serialized each series of questions/answers into one
long string (or XML) and have one record per responder, but that
would have required me later to de-serialize the results to do the
analysis.

The specifics of the analysis are not known at this time other than
it will be a collection of comparisons between "this" and "that".
Having each question/answer coupled as individual records lends
itself well using MySQL to do the analysis whereas using a serialized
string or XML would not.

To connect each series of questions/answers to the responder I used
two keys. One key was generated from time() recording the second that
user submitted the survey and the other key was a simple rand()
generated number. That way if two responders entered their survey
results at the exact same second (a race condition), then their
entries would not be likely to also have the same random number. Even
if in the unlikely event that two records had the same keys, it
wouldn't affect the analysis much.

As far as the statement made by Phpster that the OP (namely me) not
yet having good db design skills and being a "noob", I have to
chuckle. I've been doing database stuff for over 20+ years. In this
post I was just wanting to bounce ideas around and see if there was
something new. But as it turns out, nothing was presented that wasn't
considered in my original post, other than the possibility of a race
problem.

As for your offer:

>OP: if you need a mysql datamodel for reports, i'm willing to give it
>a free shot. i'm sure others here would too, or improve upon mine.
>It's probably not more than 3 tables i think.
>Let us know eh..

I appreciate the offer, but the solution was one simple table
consisting of question, answer, and two keys (as previously
described). Each participant of the survey would generate up to 180
question/answer records -- that's not a problem.

Cheers,

tedd


--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: I need a fresh look at storing variables in MySQL

On Sat, Mar 13, 2010 at 11:10 AM, tedd wrote:
> Hi gang:
>
> I just completed writing a survey that has approximately 180 questions in it
> and I need a fresh look at how to store the results so I can use them later.
>
> The survey requires the responder to identify themselves via an
> authorization script. After which, the responder is permitted to take the
> survey. Everything works as the client wants so there are no problems there.
>
> My question is how to store the results?
>
> I have the answers stored in a session variable, like:
>
> $_SESSION['answer']['e1']
> $_SESSION['answer']['e2']
> $_SESSION['answer']['e2a']
> $_SESSION['answer']['e2ai']
> $_SESSION['answer']['p1']
> $_SESSION['answer']['p1a']
> $_SESSION['answer']['p1ai']
>
> and so on. As I said, there are around 180 questions/answers.
>
> Most of the answers are integers (less than 100), some are text, and some
> will be null.
>
> Each "vote" will have a unique number (i.e., time) assigned to it as well as
> a common survey id.
>
> My first thought was to simply record the "vote" as a single record with the
> answers as a long string (maybe MEDIUMTEXT), such as:
>
> 1, 1268501271, e1, 1, e2, 16, e2a, Four score and ..., e2a1, ,
>
> Then I thought I might make the data xml, such as:
>
> 11268501271116Four
> score and ...
>
> That way I can strip text entries for <> and have absolute control over
> question separation.
>
> Then I thought I could make each question/answer combination have it's own
> record while using the vote_id to tie the "vote" together. That way I can
> use MySQL to do the heavy lifting during the analysis. While each "vote"
> creates 180 records, I like this way best.
>
> Then I thought, what would you guys do? So, what would you guys do?
>
> Keep in mind that this survey must evaluated in terms of answers, such as
> "Of the ones who answered e1 as 1 how did they answer e2?"
>
> If there is something wrong with my preference, please let me know.
>
> Thanks,
>
> tedd
>
> --
> -------

Tedd,

Sorry to be jumping in late, trying to migrate from yahoo mail to
gmail since I'm experiencing more problems with yahoo mail then I'd
like. Any way, I'd go with db storage for storing of the results
since it will give better and more flexible analysis and reporting
later. Below is how I'd do the db structure:

tbl_survey_questions:
questionId = int / uid << your call
languageId = int / uid / char << your call if you intend to I18n it ;)
question = varchar << length is your requirement
PK > questionId + languageId

tbl_participants:
userId = int / uid
userName = varchar
PK > userId

tbl_answers:
userId = int / uid
questionId = int / uid
languageId = int / uid
answer = varchar / mediumtext / or another type of text field
PK > userId + questionId + languageId

The reason why I'd structure it like this is:

Let's say you have question 1 with 5 (a-e) multiple choices, you
aggregrate your query (GROUP BY) to db for question 1 and see how many
responses are for a to e (each). If your survey is I18n and your DB
reflects it, you can even analyze how/why certain cultural background
would choose each of those answer. (don't flame me... I know the
environment comes in to growing up too :p and that's way beyond the
scope of this list )

For question 2 with could be user entry (non multiple choice
selection), again, you see what their opinions are for that question.
You get the idea as how the rest may go.

I used to do lots of reporting with the real tool, Crystal Report ;)

Regards,
Tommy

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: I need a fresh look at storing variables in MySQL

At 5:18 PM -0700 3/17/10, Tommy Pham wrote:
>-snip-
> Below is how I'd do the db structure:
>
>tbl_survey_questions:
>questionId = int / uid << your call
>languageId = int / uid / char << your call if you intend to I18n it ;)
>question = varchar << length is your requirement
>PK > questionId + languageId
>
>tbl_participants:
>userId = int / uid
>userName = varchar
>PK > userId
>
>tbl_answers:
>userId = int / uid
>questionId = int / uid
>languageId = int / uid
>answer = varchar / mediumtext / or another type of text field
>PK > userId + questionId + languageId
>
>The reason why I'd structure it like this is:
>
>Let's say you have question 1 with 5 (a-e) multiple choices, you
>aggregrate your query (GROUP BY) to db for question 1 and see how many
>responses are for a to e (each). If your survey is I18n and your DB
>reflects it, you can even analyze how/why certain cultural background
>would choose each of those answer. (don't flame me... I know the
>environment comes in to growing up too :p and that's way beyond the
>scope of this list )
>

Tommy:

The way I handled this was that all responder aspects, such as
cultural background, were all recorded before the responder started
the survey. This was part an authorization process and the responders
had to "earn" their way into the survey by providing personal data.
If they did not, then they weren't allowed to enter the survey.
Likewise, they had to turn javascript ON or they were not permitted
to continue.

Please understand that in this survey, the purpose was that the
client wasn't hoping for responders to fill out the survey (even
though they would like them to), but rather providing a method for
the membership to show their preferences in a union contract for
their collective interest. As such, responders had a vested interest
in participating. The survey would take between 20 to 60 minutes to
complete and thus required a significant time investment.

Considering that each answer (or series) could be compared to any
number of others, I thought it best to make each question/answer
created an individual record -- the table was very simple:

survey_id
question_id
key1
key2
answer

1) The union wants several surveys like this, so I provided a survey_id.

2) The question_id was simply an identifier for the question -- a
remote key to a question table.

3) Key1 and Key2 were simply values that were intended to tie the
question/answer pairs together into a single event (i.e., a vote).

4) Answer -- what we are after.

This format lends itself well to analyses using MySQL.

Cheers,

tedd

--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php