Create new Local ID or Retreive 1 from Online DB ?

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
teriibi
Posts: 254
Joined: Mon Nov 13, 2017 3:49 pm
Location: Bolivia

Create new Local ID or Retreive 1 from Online DB ?

Post by teriibi » Fri Jan 12, 2018 2:43 pm

HI,

i would like to know what steps you do use for your production App at the time of creating a new (unic) ID in an Online DB account.

I figure out it could only use 1 or the other 2 ways below, right ? :roll:

1) On device. Pressing "Create new user account" buttton,
- 1rst connect to the onilne DB...
- look for the last free id number..
- Retreives that number to the device Sqlite DB.
- Add new users infos (name,etc) to the new account localy.
- Update the online New "empty account" id with device local complete info.
- Send message to device to confirm : "new user account created"
***
..or
2) "Create a new user account localy with(or)without an Id number (? :? )
- Connect to the Online DB,
- Add the new account to the end of the Account-table.
- retreive the Final-real-id number.
- update the Device local sqlite DB with the Online ID number.
- confirm to user : "new user account created"
***

Is one way safer/better than the other ?
- I want to avoid : Useless Id created with empty accounts info.
- Inconsistent Id numbers between Online Ids and Local Users.
- Any other inconsistency in case of internet interruptions or user canceling Ops., etc..
:shock:
Tkx

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Create new Local ID or Retreive 1 from Online DB ?

Post by Mark » Sun Jan 14, 2018 2:32 pm

Make a distinction between an internal and en external ID. The internal ID is for the database and links records of different tables. The external ID is for you, e.g. a client number. The client number is stored in only one table in the database, while the internal ID is stored wherever applicable. You might want to write a trigger script that created new records with the same unique internal ID number in every table.

- set up the database such as to create new unique ID numbers automatically
- insert new record
- retrieve data by client ID (as in above example)
- use internal ID number to retrieve data from other tables

If you happen to make a mistake, you might enter a duplicate client number while the new records are still correctly linked by the internal ID number.

Don't make your database more complicated than really necessary!
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

teriibi
Posts: 254
Joined: Mon Nov 13, 2017 3:49 pm
Location: Bolivia

Re: Create new Local ID or Retreive 1 from Online DB ?

Post by teriibi » Mon Jan 15, 2018 3:36 am

Thanks Mark, So the internal number can use the Auto_increment Field property, while the External would need to be coded to emulate such unic number ?

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Create new Local ID or Retreive 1 from Online DB ?

Post by Mark » Fri Mar 09, 2018 11:33 pm

Why "emulate"? That concept doesn't make sense here. Internal and external ID's are just different things with different purposes. As I said, don't make it more complex than necessary.

Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

teriibi
Posts: 254
Joined: Mon Nov 13, 2017 3:49 pm
Location: Bolivia

Re: Create new Local ID or Retreive 1 from Online DB ?

Post by teriibi » Sat Mar 10, 2018 4:15 pm

The external ID is for you, e.g. a client number. The client number is stored in only one table in the database,
By "Coding to emulate" here , I mean that there must be a script used at some time to "fabricate" a client ID that is a litle more smarter than just just starting with :

- 1rst Client added to the empty Table will inherit Id number 1.
- 2nd Client in the Table will inherit Id number 2...
:wink:

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Create new Local ID or Retreive 1 from Online DB ?

Post by Mark » Sat Mar 10, 2018 6:27 pm

Well, no, why would it have to be smarter than that? The point is just that sometimes internal and external ID's aren't the same, so if you decide to use an external ID like a customer number, you often may want to consider this a field just like any other and use the internal, automatically generated, ID to make queries involving multiple tables. That's really all there is to it and there is no such thing as "emulate" or "fabricate".
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

teriibi
Posts: 254
Joined: Mon Nov 13, 2017 3:49 pm
Location: Bolivia

Re: Create new Local ID or Retreive 1 from Online DB ?

Post by teriibi » Mon Mar 12, 2018 2:56 pm

Well, no, why would it have to be smarter than that?
..sql injection, using too easy guessable IDs !

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Create new Local ID or Retreive 1 from Online DB ?

Post by Mark » Mon Mar 12, 2018 3:29 pm

Usually ID's are easy to guess and protection against SQL injection isn't done by complicated ID numbers.
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

SparkOut
Posts: 2834
Joined: Sun Sep 23, 2007 4:58 pm

Re: Create new Local ID or Retreive 1 from Online DB ?

Post by SparkOut » Mon Mar 12, 2018 10:28 pm

What Mark said. If someone can break/hack your database because of guessing an id they already have more access and control than they should. I'm not sure I understand your exact needs though.

teriibi
Posts: 254
Joined: Mon Nov 13, 2017 3:49 pm
Location: Bolivia

Re: Create new Local ID or Retreive 1 from Online DB ?

Post by teriibi » Mon Mar 12, 2018 10:48 pm

...guesssing values is part of a brute force breaking process, By having (guessed) a single user ID of your table it may be enuff for sneakers to get this complete user info alone - but not all others users from the same table - while if youd use a simple sequencial numbers, this would just makes breaking into all other users accounts much more easy for them.

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Create new Local ID or Retreive 1 from Online DB ?

Post by Mark » Mon Mar 12, 2018 10:52 pm

Whatever. If you think you know best, then why did you ask your question in the first place.
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

teriibi
Posts: 254
Joined: Mon Nov 13, 2017 3:49 pm
Location: Bolivia

Re: Create new Local ID or Retreive 1 from Online DB ?

Post by teriibi » Mon Mar 12, 2018 11:00 pm

Take it easy Mark :wink:
Actually you taught me there were two types of Ids, internal and external - which I didnt know before you mentioned it.
..which in turn led me to search how should I enforced them..that´s all. I´m not saying keeping things simple is bad.
i believe it all depend of the time and level of "Paranoia´s details" one wants to build its app with when it comes to the security side..and I´m probably one with a good level of paranoia ! :lol:
no offence... :mrgreen:

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5150
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Re: Create new Local ID or Retreive 1 from Online DB ?

Post by Mark » Mon Mar 12, 2018 11:12 pm

No, you're not at a good level of paranoia. You're the type who would kill his app for security's sake. First learn something about threats and security and then decide which level of security you need. Leave out terms like paranoia. That's not a term that should be used in software engineering and even less so in science.
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

Post Reply

Return to “Databases”