locked
Storing database tables in memory RRS feed

  • Question

  • Hi, i'm developing a login server program using mysql, and i wonder if its better to store the whole 'users' table (contains id, username, password and email) in memory as a list<struct>, or if i should do a query each time for cheking user/password. I guess that checking the user/password in a list<struct> its faster for small lists but i don't know for a big list.
    Sunday, October 18, 2015 10:48 AM

Answers

  • I dont really know how anything of this works, for what I understood you say that i should save the passwords in the DB encrypted, but it is ok to save the encrypted passwords in memory? I've done a test and i think it's faster to save the user info in memory (takes about 90ms to read 50000 accounts, and not even 1ms to check a user/password)

    And that is opposed to what? You don't save sensitive data like that in memory. Do you think a bank or any other organization that is concerned with security and computer programming would be so careless in security for the sake of speed that you propose in doing?

    What you are talking is too careless, and you should be stopped is the bottom line.

       
    • Marked as answer by Pau CP Sunday, October 18, 2015 6:22 PM
    Sunday, October 18, 2015 12:23 PM
  • Storing DB tables in memory is on average a terrible idea. Sure you get speed.
    At the cost of transaction reliability, total loss of data on power down, and possibly overtaxing your servers memory*. And if your connection is slow enough you end up being slower (fist run vs throughput). If you got multiple copies that all support editing you end up having to do merging and similar stuff.

    *.NET Server programms tend to run into OOM exceptions a bit more regulary then other servers or other .NET Programms. Storing tables in memory is a common cause for this:
    Troubleshooting System.OutOfMemoryExceptions in ASP.NET : Microsoft Support Team's IIS Blog : The Official Microsoft IIS Site

    A proper DB Engine already has so many layers of caching and optimisations, just running it on the same machine it will effortlessly catch up to anything you can make up. At the same time it is tried and reliable, rather then some custom writeup with custom bugs.
    Even if the DB is just in the same network, it will be sufficient for most cases.

    Keep the DB data to the DB is my moto.
    Do as much filtering and processing as you can in the DB/SQL query. That is the best rule for working with DB.
    Look into how you can circumvent potential speedblocks on the DB level, not the application level. But that is best asked on the SQL Forums, they know a few tricks.

    "I've done a test and i think it's faster to save the user info in memory (takes about 90ms to read 50000 accounts, and not even 1ms to check a user/password)"
    And it would be even faster to just do that work in a SQL query, only returning the results it actually needs to the programm. Rather then 50k Rows it does not need anyway just to do some conditional counting.

    On storing passwords:
    Storing in plain text is just wrong on so many levels. Storing the hash or encrypted value of the password is really the least you should do, but even that can be easily broken.
    Password+salt in hashed form might be better, but it depends where you store the salt then.
    The best way is to have somebody else store this for you.
    Here is a nice video on the thematic:
    https://www.youtube.com/watch?v=8ZtInClXe1Q

    • Marked as answer by Pau CP Sunday, October 18, 2015 6:22 PM
    Sunday, October 18, 2015 1:12 PM

All replies

  • Better is not a term that applies.

    What are your requirements?

    btw, storing passwords is a poor, bad practice.. store a salt and hash instead.

    Sunday, October 18, 2015 11:08 AM
  • So it can get hacked? You should be hitting the DB for the credentials.
    Sunday, October 18, 2015 11:26 AM
  • I dont really know how anything of this works, for what I understood you say that i should save the passwords in the DB encrypted, but it is ok to save the encrypted passwords in memory? I've done a test and i think it's faster to save the user info in memory (takes about 90ms to read 50000 accounts, and not even 1ms to check a user/password)
    Sunday, October 18, 2015 12:11 PM
  • I dont really know how anything of this works, for what I understood you say that i should save the passwords in the DB encrypted, but it is ok to save the encrypted passwords in memory? I've done a test and i think it's faster to save the user info in memory (takes about 90ms to read 50000 accounts, and not even 1ms to check a user/password)

    And that is opposed to what? You don't save sensitive data like that in memory. Do you think a bank or any other organization that is concerned with security and computer programming would be so careless in security for the sake of speed that you propose in doing?

    What you are talking is too careless, and you should be stopped is the bottom line.

       
    • Marked as answer by Pau CP Sunday, October 18, 2015 6:22 PM
    Sunday, October 18, 2015 12:23 PM
  • Well, that's right, but as i said i dont know anyhthing about databases, servers, security or whatever. I'm just doing a test application to learn a little about it (I've just learnt a couple of things today).

    Then i'll do that, not save important info in memory. Thanks for the comment.

    Sunday, October 18, 2015 12:46 PM
  • Well, that's right, but as i said i dont know anyhthing about databases, servers, security or whatever. I'm just doing a test application to learn a little about it (I've just learnt a couple of things today).

    Then i'll do that, not save important info in memory. Thanks for the comment.


    And you had better figure out how to protect MySQL and DB tables from a SQL Injection Attack where the hacker hacks through your program and hacks the data at the database level by running a SQL Injection Attack on the database through your program.  :)
    Sunday, October 18, 2015 12:56 PM
  • I'm looking on internet and seems that i can protect against SQL injections by detecting special chars in the command string ( ' and -- ) and not execute the command if they're found (i use the id/username/pw so it shouldn't be any special chars), and also using parametrized queries
    Sunday, October 18, 2015 1:07 PM
  • Storing DB tables in memory is on average a terrible idea. Sure you get speed.
    At the cost of transaction reliability, total loss of data on power down, and possibly overtaxing your servers memory*. And if your connection is slow enough you end up being slower (fist run vs throughput). If you got multiple copies that all support editing you end up having to do merging and similar stuff.

    *.NET Server programms tend to run into OOM exceptions a bit more regulary then other servers or other .NET Programms. Storing tables in memory is a common cause for this:
    Troubleshooting System.OutOfMemoryExceptions in ASP.NET : Microsoft Support Team's IIS Blog : The Official Microsoft IIS Site

    A proper DB Engine already has so many layers of caching and optimisations, just running it on the same machine it will effortlessly catch up to anything you can make up. At the same time it is tried and reliable, rather then some custom writeup with custom bugs.
    Even if the DB is just in the same network, it will be sufficient for most cases.

    Keep the DB data to the DB is my moto.
    Do as much filtering and processing as you can in the DB/SQL query. That is the best rule for working with DB.
    Look into how you can circumvent potential speedblocks on the DB level, not the application level. But that is best asked on the SQL Forums, they know a few tricks.

    "I've done a test and i think it's faster to save the user info in memory (takes about 90ms to read 50000 accounts, and not even 1ms to check a user/password)"
    And it would be even faster to just do that work in a SQL query, only returning the results it actually needs to the programm. Rather then 50k Rows it does not need anyway just to do some conditional counting.

    On storing passwords:
    Storing in plain text is just wrong on so many levels. Storing the hash or encrypted value of the password is really the least you should do, but even that can be easily broken.
    Password+salt in hashed form might be better, but it depends where you store the salt then.
    The best way is to have somebody else store this for you.
    Here is a nice video on the thematic:
    https://www.youtube.com/watch?v=8ZtInClXe1Q

    • Marked as answer by Pau CP Sunday, October 18, 2015 6:22 PM
    Sunday, October 18, 2015 1:12 PM
  • Thanks for the comment, I'am already trying to do all of this (except someone else storing salt) but I'm soooo slow writing code.
    Sunday, October 18, 2015 1:24 PM
  • Thanks for the comment, I'am already trying to do all of this (except someone else storing salt) but I'm soooo slow writing code.

    https://en.wikipedia.org/wiki/Separation_of_concerns


    Sunday, October 18, 2015 4:08 PM
  • Hi, i'm developing a login server program using mysql, and i wonder if its better to store the whole 'users' table (contains id, username, password and email) in memory as a list<struct>, or if i should do a query each time for cheking user/password. I guess that checking the user/password in a list<struct> its faster for small lists but i don't know for a big list.

    Why would you store the full table in memory? A user logs in once, so that is a once-off trip tot the database. The query that you run can return anything that you need for that specific user (e.g. userID. userName and userEmailaddress; not the password, no need for it).

    Store that information in a 'global' variables that you can access from anywhere in the code.

    You can set a default of -1 for the global variable that stores the userID so you know if a user is logged in or not.

    Sunday, October 18, 2015 4:36 PM
  • Store that information in a 'global' variables that you can access from anywhere in the code.

    That is not a good practice.

    http://koopman.us/bess/chap19_globals.pdf

    Sunday, October 18, 2015 5:54 PM