I’m not a web app developer, but I am an IT security guy, so I can talk in more general terms.
The proper way to store passwords is by using a hash function, otherwise known as a one-way or trap door function. So you take the plaintext password input, put it through a secure hash function, and you get back a “hash” of the password.
Hash functions are known as one-way functions because plaintext can pass through the function into a hash value, but there’s no good way to take that hash value and use it to get back to the original plaintext. But a hash function will generate the same hash value for the same plaintext value every time.
Hash functions will always produce the same length hash for any cleartext value. So whether my password is 1 character long or 1,000,000 characters long, the hash function will generate a fixed length value. Since you are taking an infinite number of input values and turning it into a hash that’s a finite number of characters long, you can see why it would be impossible to take that finite hash and turn it back into one of an infinite number of values.
So, from a web site perspective, the first time a user enters a password for the site, the web app takes that password, hashes it, and stores it in a database. The next time the user logs in, the system hashes the newly entered password and compares it to the stored hash value. If the stored hash matches the newly computed hash, you let the user in because they gave you the same password.
There are some deeper concepts in hashing. One is the idea of hash collisions. Since you are taking infinite input and producing finite output, there are going to be many input values that would produce the same hash. Understand that this doesn’t make the hash function insecure by any means. It is like any branch of crypto in that you are hiding secrets in plain site, knowing that the math needed to turn the hash into something useful is extreme.
Another hash concept is the idea of adding a salt value to the hashing mechanism. To salt the hash, you add a value to the hash algorithm that is known. Why? Hashes can be precalculated, and in many cases, have been precalculated. Example: my password is “password” and hashed it becomes “1q2w3e4r5t”. An attacker can take a list of commonly used passwords (called a dictionary) and hash all of those words. If I break into your system and steal your password hashes, I already know that “1q2w3e4r5t” is the output of the hashing mechanism for the word “password”, so if I find that string in any password hash fields, I know that the password is “password”.
To make those calculations more difficult, the hashing mechanism may add a salt, one of X values. This value is stored in the clear with the hash, but it makes the precalculation of the hashes much more difficult. My password of “password” may become “1q2w3e4r5t” with a salt value of one, but it will become something different with a salt of 4096. So now an attacker must precalculate multiple values for each plaintext, which makes this “dictionary attack” much more costly from a processing standpoint.
To answer your questions:
How does the site store the data on my server? It can be stored in any fashion (UNIX passwords are still, in many cases, stored in a plain text file), but a database is the right tool for this.
Does it use SQL? It can. Most modern web apps use some back-end database to store all important information.
Do I have to write my own database or is it part of the package? Grab MySQL or something similar and use that. Writing a new database is significantly non-trivial.
Do I have to code the lookup/retrieve functions or again is it part of a package? You’ll pull the user authentication information out of the database using a standard query and use it to compare hashes, set privileges, etc.
How do I lockout someone without the proper password or maybe the better question is how does a person gain access to a page after login? A lockout value should be stored in your database, so every time a user gets a password wrong, that lockout value increments until either the user gets in, or you lock them out because that value got too large. The user gains access when new password hash value matches the stored hash value.
Another question is how standard is all of this? Very standard. There are lots of frameworks out there that have all of this stuff already written and tested.
Are there one or two techniques/packages that everyone uses or a ton of different ones or everyone writes their own? For password hashing, everyone uses one of just a few studied and vetted hash algorithms. Most of these have already been coded in just about any language you’d care to use, so you can just drop a pre-made chunk of code into your program. NEVER NEVER attempt to write your own encryption methods.
Another is if data is stored with the security data or if that is a separate database. This depends on how you design your system. Generally, it would be the same database.
Yet another is are there questions I should be asking? If you are looking to process credit cards, the project will get a lot more complex unless you use a third-party (like Paypal) to process for you. As soon as you touch credit card information, you are required to go through the PCI-DSS process to ensure you are doing the right things to protect the data, and that can get to be a HUGE burden.