SSL certificate key with mysql and php

Hello,
I would like to generate SSL certificates, store the key inside mysql database and use php to compare the given key in database with the key that the client has installed in their browser.
Now for certificate generation I can handle that but how about storing the key inside database and how to let php compare between the given key and the one stored in db ?
adra750Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave HoweSoftware and Hardware EngineerCommented:
Storing a key in DB isn't hard. Comparing the key doesn't make sense though; if you are talking about clientside certificate usage, normally you sign the cert with your own (internal) CA, send the CA's name in the list of acceptable CAs during the SSL handshake, and the SSL library handles verifying the client actually has a cert and the key for that cert. Once verified, the fields in the clientside cert are exposed to the php code using the listener.  If you aren't handling your own SSL, then you will have to rely on the host engine (apache, say?) handling all that for you, and exposing the client cert's fields to you.
0
gr8gonzoConsultantCommented:
Sorry, but that's wrong. There is no "list of acceptable CAs", and the CA isn't involved at all during the SSL handshake. Nor does the SSL library verify that the client has a cert and key.

With SSL certs, you have a public key (stored in a certificate), and a corresponding private key.

Public key does two things:
- It encrypts data that can be decrypted by the private key.
- It can verify a data signature created by the private key.

Private key does the complementary two things:
- It decrypts data that has been encrypted by the public key.
- It can generate a data signature for a given piece of data.

The CA is only there to establish trust for certificates that it has generated. So if you have "Joe Bob CA" installed on your own computer as a trusted CA, then YOUR computer will trust any certificates that have been issued by Joe Bob CA. A computer that did not have Joe Bob's CA installed as a trusted root CA would be unable to trust those same certificates. All major operating systems come pre-installed with a list of trustworthy CAs like VeriSign, so that there's a starting level of trust.

When you want to be able to identify which client sent you a piece of data and be able to trust that nobody has tampered with the data, you can use a public/private key pair for this. Basically:

The client generates a new key pair, which gives them both the public and private key. They keep the private key and never share it with anyone. They give the public key to anyone else (e.g. they upload it into your database). When it comes time to send along trusted data, the client creates the data to be sent, then signs it with their private key, then sends it off. The receiving end takes the public certificate/key from the database and uses it to validate the signature on the data. If it's valid, then the data hasn't been tampered with and did indeed come from that particular person.

Ideally, before any validation takes place, the person that is using the public certificate will verify that they can trust all of the certificates in the chain that created that public certificate. It's a step that usually takes place immediately before public certificate usage, and it applies only to the computer that is about to use the certificate. So that particular computer will need to have the CA certificate installed in the trusted root CAs.

The trick is that you really shouldn't generate public/private key pairs for your end users. They should be the ones generating them. Any transmission of the private key puts it at risk. If your users don't already have a way of doing it themselves, your best bet is to use an HTTPS transmission where the browser and server both have their own key pairs and are using it for mutual, secure data transmission. This way, at least the private key gets downloaded over a secure connection.

In any case, that's the way that keys work.
0
Dave HoweSoftware and Hardware EngineerCommented:
0
Discover the Answer to Productive IT

Discover app within WatchGuard's Wi-Fi Cloud helps you optimize W-Fi user experience with the most complete set of visibility, troubleshooting, and network health features. Quickly pinpointing network problems will lead to more happy users and most importantly, productive IT.

gr8gonzoConsultantCommented:
@Dave - Very carefully re-read those steps that you posted and tell me what you think is happening or where you think I'm wrong.

The MAIN difference between client and server auth is the CertificateVerify message.

Per the Wikipedia article you linked to:
"The client sends a CertificateVerify message, which is a signature over the previous handshake messages using the client's certificate's private key..."

So at this point in the handshake, the client has its private key, and it has sent a copy of its public key to the server. Now the client creates a message and signs it with its private key.

"...This signature can be verified by using the client's certificate's public key. This lets the server know that the client has access to the private key of the certificate and thus owns the certificate."

The server uses the public key that it got to verify the signature of the CertificateVerify message. Since the client is the only one who should have the private key, the signature validation also validates the client's identity and ownership of the key.

Take a look through those steps and tell me where the CA gets exchanged.

The SSL handshake for client auth only cares about verifying the ownership of the key. The public key/certificate is already a required part of getting the handshake going. It's like saying that seeing a car driving down the highway validates that the driver has the key to that car AND that the car is visible to your eyes. The key/ownership is the important part - the other half is just what makes the validation process available in the first place.
0
adra750Author Commented:
I need to setup an extra layer of security other than the login and password, I want to generate a certificate and send it to the customer in order for him to upload it in browser like Firefox then be able to login. If you don't have the certificate in your browser even if you have the correct login and password you will be denied ... I hope I explained my needs.
0
gr8gonzoConsultantCommented:
Well again, the transmission of the certificate and key are risky. Also, there's really no way for a user to automatically install a client certificate in all of their installed browsers (at least not without some 3rd party program), so even if you generated a key pair for them, they still need to go through manual steps to import it into their browser to be used as a valid client certificate.

It's just not a simple thing to do for people who are not part of your own company/organization. I'm assuming that if you are calling them "customers", that they are individuals who may belong to different companies and organizations. This could also mean that some of them have restrictions from being able to install a client certificate.

How big is your customer base (tens, thousands, millions) ? Also, how much control do you have over them? For example, is it even feasible to ask them to follow some manual steps to install a client certificate? Could you ask them to user a multifactor token like Yubikey, or could you even send them one?

Any good security step should be based on mutual cooperation. So if you are trying to force your own security measure onto people who only know you as another vendor, then it's going to be difficult for them to accept unless they have no option or they have a REALLY good relationship with you.
0
Dave HoweSoftware and Hardware EngineerCommented:
@gr8gonzo: the line from the wiki:
"The server requests a certificate from the client, so that the connection can be mutually authenticated, using a CertificateRequest message."

is the key here. if you check the standard (rfc2246 7.4.4 for example) you will see this:

    certificate_authorities
           A list of the distinguished names of acceptable certificate
           authorities. These distinguished names may specify a desired
           distinguished name for a root CA or for a subordinate CA;
           thus, this message can be used both to describe known roots
           and a desired authorization space.

The client then responds with a certificate of its choice (signed by one of the listed CAs) using the Certificate message, then sends the CertificateVerify you reference above.

Use wireshark to pull out the conversation and you can see all of this happening - its quite informative :D
0
Dave HoweSoftware and Hardware EngineerCommented:
@adra750: it is more usual  to rely on the identity within the certificate alone; you CAN include a conventional (realm, or even form based) authentication too, if you wish (or if you don't trust the client not to install his cert without password protection)

Of course, it is considered poor practice to generate and send the user a private key; you might instead want to have a web based (eg OpenCA) form so that the browser can generate the key and send you the CSR for fulfillment (you probably wouldn't want to automate the issue, but it is fine for it to queue up the CSRs for you, and you can email the completed certificates back)
0
adra750Author Commented:
Let me tell you a very short story from my own personal experience, I've been working with an IT company, one day, a customer came and asked help to import his SSL certificate provided by the treasury ministry. They gave him a link on a paper to download that certificate, but before I installed it, I asked him to login just about curiosity I wanted to see what would happen, I tried the login and pass without the SSL, access was denied, then I download it the certificate I imported it into firefox, I tried to login and it was successfully granted ... So what do you think about this.
0
Ray PaseurCommented:
Not sure what you're trying to protect, but this design takes the login/password a step further by sending a key to the registered email address and asking the owner to verify his identity.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_3939-Registration-and-Email-Confirmation-in-PHP.html
0
adra750Author Commented:
I'm looking to add another layer of security

Step One: Log with your login and password
Step Two: I need a mechanism to check an installed Certification in the browser if it's authorized to be used by the user even the login credentials are correct. I need to store this certificate key inside the database and compare it with the one provided to the user, if it matches then Ok login is successful, if not, login will not be allowed.


I think that I explained myself in the very informal way in plain english...
0
Dave HoweSoftware and Hardware EngineerCommented:
well, then yes.

The certificate exchange will happen *first* - it happens when https is first set up, and the browser may or may not prompt for a choice of certificate depending on its configuration.

once running, you can then proceed with a normal check-cookie-and-redirect-to-login as is convention for handling login details; you have the additional ability to use the values in the supplied certificate (if you are running apache, this will be in $_SERVER as SSL_CLIENT_xxx named entries - if you make the cert optional, then SSL_CLIENT_VERIFY will flag if a client cert was supplied) and compare the cert to the supplied login, or simply force the username to be taken from the cert (i.e. display it but require a password to login)
0
adra750Author Commented:
Alright, can you provide me with a link if someone did this before?
Thanks Dave for your efforts
0
Dave HoweSoftware and Hardware EngineerCommented:
Too general a question really. If you specify the webserver, I can probably find you a page on getting clientside certs up and running (apache, say) and if you are using a common web framework, there are probably pages on that too.

However, if I were doing this for an existing web app, I would go to the login php page, locate the form element for the login name, and remove it - The form that processes the login can verify SSL_CLIENT_S_DN_x509 as the username (so replace the submitted user field with that) and of course the submitted password. This means you can perform minimal changes to the architecture of the system, and use the client cert to supply the username instead of the user entry.
0
adra750Author Commented:
Yes but I need to use them both, the login/password + certificate key authentication

If you have the key of a car, it doesn't mean always that you own it, usernames and password can be sniffed and people are doing their best to steal these information to access someone's account. But, in the other hand, the certificate key will be like a fingerprint that confirm that you are the owner, unless your computer is stolen or someone accessed it physically.

You see what I mean?

I will be running a linux server where I will be generating those certificates for each of my users. But, what next? How to store the key inside mysql ? how to compare it while the user is trying to authenticate? I will be using Laravel Framework.

That's why I asked if there is a tutorial if someone tried to implement this.
0
Dave HoweSoftware and Hardware EngineerCommented:
you don't need to store the key anywhere - the end user will prove they have the key by using it at the ssl level; the username is *in* the certificate, which they also supply, so getting them to re-enter it each time would be pointless. By the time your server actually sees them at the login page, they have already proven possession of their client certificate and its key to your server, and the server has made available to you the username from inside the certificate so you can use it to identify which certificate they have used; you can re-enter that data (and compare it) if you wish, but it doesn't add anything to the process (unless I am misunderstanding something here)

so process would be:

1) you (by some method, using XCA is easiest) create your own CA and issue a client cert to each user, which has the user's name or other unique identity as its subject)

2) you upload the CA cert (but NOT its secret key) to your server, and define that as the CA reference file for your webserver

3) you set your webserver to perform HTTPS, and *require* a client cert. At this point, nobody can access the https on your webserver unless they have a client cert signed by *your* CA, which you keep offline as far away from your webserver as possible.

4) once connected, the user's name or other unique identity (from the certificate) is available to your app to validate; normal operation would be to notice they aren't yet logged into your site (cookie, probably) and redirect to a login page. This will be standard for your framework, so doesn't require you to do anything special

5) once they arrive at the login page, then you *already* (from the apache SSL_CLIENT variables) know what is in the certificate they gave to gain access; you can use that info in place of username, but still prompt to enter a password. You don't HAVE to do that (you could just ask them to re-enter the username) but odds are good they will just have that saved on the browser anyhow.  If you want *extra* security, that password need not even be fixed; it is easy enough to implement a 2FA token (using RFC 6238, aka "google authenticator") in place of a fixed password, so that they need a physical token (or more usually, the smartphone app for GA) in order to auth instead of a password.
0
adra750Author Commented:
Sorry for this delay I was extremely busy...

I assure you that I will still need to store the key in the database here is why:

Imagine I have 2 applications in my website

Application X and Application Y

Application X is used by a group of users under an admin account 1.
Application Y is used by a group of users under an admin account 2.

Keep following... I need to create a key for every user when admin 1 asks for it (automated process) for his users in order to access Application X. The checking of key will be done when you try to access application X so we can know who you really are. You can say why don't you use permission etc.... Yes I thought about it, but let's say you logged in and you have an application that store your credit cards numbers for example, if you get only the login and password I can log in and get some numbers... But with the idea I wanted to implement if I got your username and password even though I won't have access to that application because I'm required to show some identity... You see what I mean?
0
Dave HoweSoftware and Hardware EngineerCommented:
No, I still don't follow - you seem to have a misunderstanding of how browser-side certificates work.
the browser sends it's public key, and the rest of it's certificate, selecting which to send based on the CA spec you send during the SSL handshake.  It proves it has the private key (which ideally you never have) by digitally signing data with it, and you use the public key in the cert to verify that. and when I say "you" - I mean your webserver does that for you, transparently, before it calls your own code.

If you want to verify the public key yourself, you are still going to have to supply the certificate signed by your CA (or the browser won't send it, and hence won't digitally sign the data) *and* you will have to code up the entire SSL transaction yourself - that is a lot of work.

you can of course access the public key from the supplied cert after the fact, and verify it against a database entry (it will just be a hex blob of the appropriate size) if you want to - but not seeing why you would want to? the webserver will do all the validation for you, out of the box, and hand you the info from the cert in a data blob for identity purposes.
0
adra750Author Commented:
Ok, but did you understand what i want to achieve, it's the same concept or probably something like VPN, you have the CA.crt which is unique and public and you have a key generated for every user in order to authenticate and the CA.crt is one of the requirement.

Anyway forget about that I don't want to ruin your brain with this, but basically what i want to achieve is what I described earlier in my post ID: 40739970

So I come up with this idea.

If you are logged in from a location you never logged in from, you won't be able to access your account until you provide the verification code sent to email. That's fine.

Now, if you accessed your account and you wanted to access Application X, I want that the user already has a key cert uploaded in their browser, this cert is generated ONLY for this user, which means, if another user tried to use it, it won't work and should not work but only if you used user1's login credentials , that is the case if the user changed his computer/formatted etc...

So my requirement again is simple:
I use login authentication using code sent to email
Access to application is provided after I check the cert key and now the question still remains, how to verify that the user has the cert that was generated first time?
If the user reported that their pc for example is stolen, i will revoke the cert and recreate another one, the old one cannot be used even if the thief tries to.

I hope I explained it slowly and you got my idea
0
Dave HoweSoftware and Hardware EngineerCommented:
Normal operation is that their web browser will send you the cert, which is generated for only that user, and prove possession of the private key - which would be the checking bit. If you revoke the cert, then the cert will no longer validate (again, standard SSL/X509 mechanisms) so they will be unable to auth to the webserver with it (and/or you can alter the data in the new cert you issue, and put the old data in your DB as a reference to the now-invalid cert). If the cert is valid, then you can use the SSL_CLIENT fields to pull the info in the cert (and hence, which cert they presented) in your code, and act appropriately; you can compare that data against what you have stored for that one user, to confirm that they presented the right cert to you.
0
adra750Author Commented:
exactly Mr Dave, that's right (Y)

Ok, I'm lost little bit I don't know from where to start, can you give me word keys, did you achieve something like this before? or have someone posted an example like that?
0
adra750Author Commented:
*Keywords
0
Dave HoweSoftware and Hardware EngineerCommented:
a google search like This One should get you a few examples :D
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
adra750Author Commented:
thank you very much
0
Dave HoweSoftware and Hardware EngineerCommented:
NP. Its an interesting concept, and one most people don't explore; TLS has some significant features that are rarely if ever used, and that is a shame.
0
gr8gonzoConsultantCommented:
I just came back to this today after a hellish week.

@Dave - I re-read the threads starting at the top. First, I was definitely wrong, and I'm not quite sure what I was thinking last week. My only guess is that I was reading this:
"...if you are talking about clientside certificate usage, normally you sign the cert with your own (internal) CA, send the CA's name in the list of acceptable CAs during the SSL handshake..."

...as you saying that the client sending over a list of acceptable CAs instead of the server doing it, which wouldn't make sense. That's why my response referred to the server using the public key to verify, since it was already trusting that key and any issuing CA.

Whatever the case may be, I was off my rocker last week. Sorry about the confusion. Glad the OP got his/her answer.
0
Dave HoweSoftware and Hardware EngineerCommented:
NP. In context, you (the server and/or the server's owner, as appropriate) have your own CA, which is used to sign client certificate requests and issue certificates. your server sends a list of acceptable CAs (i.e. your own, so it's a short list) to the client as part of the handshake; the client is free to offer any certificate it has that is signed by that CA, but how it selects one (if there is more than one) is client specific.  It's a fun idea, and I am disappointed that more people don't use it, given it's effectively free (other than the overheads of actually setting it all up and issuing certs, of course)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.