[Wolves] mysql select help

Chris Ellis chris.ellis.intrbiz at googlemail.com
Tue Mar 13 23:19:58 UTC 2012


Hi Wayne

On 13 March 2012 02:45, Wayne Morris <waynelists at machx.co.uk> wrote:
> On 13/03/2012 01:52, Chris Ellis wrote:
>>
>> Can you share your schema? It makes it easier to assist in SQL problems
>> when you know what the table structures are.
>
> My structures are at best 'pitiful' lol, its a work in progress - only 400+
> current records so haven't worried about spending too much time optimising
> fields,

Everything has to start somewhere.  Most things start out simple and evolve.

> eg addresses are all pretty much Wolverhampton, but do I use a linked table
> for towns...noooo ;-)  . when I get to 4000+ i hope to have enough money to
> pay someone to write it properly lol.
> So, one field one item, functional rather than 'neat' ;-)

I doubt you will ever have enough rows to have to really worry about optimising
queries to that extent.

>
> Basically its a simple address book for booking in my scuba students, so
> just who, when, what type of stuff, about 30 fields - have quite an
> elaborate set of filters on a webpage protected by htacess to sort and
> produce print lists for internal use - and a simple 'add yourself' page
> which students can access in an open directory.

Sounds cool, I'm always an advocate of building your own tools,  I'm quite good
at doing that. It also always fun to learn.

One thought does come to mind, have you considered the likes of SugarCRM ?
Would that have the kind of functionality you need.

>
> Got a lot to do to it, security is the next issue - currently I only allow
> students to add themselves database, no editing, the data isn't sensitive
> (well, no credit cards or the like) so the worst they can do is add non
> existent person.
> But I want to add 'edit your own record' and since my current setup is that
> update.php produces a POST link of eg   website/update.php?id=356 , I know
> this
> is enough to let the naughty inject another ID to edit someone else's
> record.
> Since I only need reasonable privacy (nothing of value but info available on
> 192.com to steal) would I be ok:
> 1)Finding some way of randomising ID key, if it was 10 digits, it would be
> nearly impossible to guess one of the other 400 users ID's
> 2) provide them with a link comprising first name, 2nd name and dob,
> passwordrd fieldd so still breakable if someone knows them, but still tough
> 3) something else?

As a way to keep it simple and kinda secure.  I'd recommend you use some form
of random token.  So, for each user generate a random token, quite
long, 80 characters
or so would have enough enthalpy.  Store this token in the DB along
with the user.

Generate an edit link for the user in the form of:
uid=123&token=hfkjjhd....ddjjd.  Email
this to the user.

When your server receives the request check that the token matches
what u have in the
db for the user id.

(don't use the PHP rand() function, its not very random and avoid MD5)

I would think *very* carefully before you send out peoples date of
birth and name.  Remember
that a name, email address, etc count as personal data.  Especially
when correlated together.
As such you must treat that data with care, as per the Data Protection Act.

>
> I don't really want to go down the user log on bit as it confused the hell
> out of me last time I did one, and they at most need a one time edit
> facility to
> add details they screwed up on adding themselves in the first place so it
> doesn't seem worth the effort .
>
> I know the proper answer is 'do it right' but I'm just trying to bash enough
> code together in my spare time to get by, so easy and enough is good ;-)

There may well be existing libraries you can use for user auth.  There
are a number
of PHP frameworks out there, but these will require time to get going with them.

Also, have a look at what Linus has been upto, he has a recent
interest in diving.

>
>
> cheers
>
> Wayne
>

Chris



More information about the Wolves mailing list