What Will I Learn?
Implementing an input mask in MySQL Server
Requirements
MySQL Server
Difficulty
Intermediate
Tutorial Contents
A step by step tutorial on how to implement an input mask in MySQL Server
How to implement an input mask in MySQL Server
People often forget that Access is both a front-end and a back-end tool. MySQL Server is only a back-end tool, designed for data management and NOT data presentation. So, there are several other approaches you will need to choose from. Some of these will assume that you really want to secure and protect the underlying data, not just make it look pretty (e.g. in case users use profanity in their passwords).
Store an encrypted version
Of course, if you need to later retrieve the data, encryption is only as strong as the place where you store your decryption algorithm. In fact, many will argue that encryption that can be reversed should not be called encryption at all, but rather "encoding" or "obfuscation."
In the case of passwords, there really is no reason to store the password in plain text. You can easily store an encrypted version, and when verifying permissions as the user logs in, apply the same encryption to the password they submitted. Then, you compare the encrypted versions and make sure they're equal, instead of comparing the plain text versions.
My recommendation is to use available APIs in your application to encrypt the data BEFORE you pass it to the database. This prevents the plain text data from going across the wire. Here is a quick example.
First step: I created this table and stored procedures:
CREATE TABLE dbo.Users
(
email VARCHAR(255)
PRIMARY KEY CLUSTERED,
pass_word VARCHAR(255)
)
GO
CREATE PROCEDURE dbo.AddUser
@email VARCHAR(255),
@pass_word VARCHAR(255)
AS
BEGIN
SET NOCOUNT ON
INSERT dbo.Users
(
email,
pass_word
)
SELECT
@email,
@pass_word
END
GO
CREATE PROCEDURE dbo.GetUser
@email VARCHAR(255)
AS
BEGIN
SET NOCOUNT ON
SELECT email, pass_word
FROM dbo.Users
WHERE email = @email
END
GO
Step 2: Next, I grabbed the MD5 code and placed it into a file called MD5.inc.
<!--#include file="MD5.inc"-->
<%
set conn = CreateObject("ADODB.Connection")
conn.open "<connection string>"
submittedEmail = "[email protected]"
submittedPassword = "foobar"
Step 3: Let's create a hashed password
sql = "EXEC dbo.AddUser '[email protected]', '" & MD5("foobar") & "'"
conn.execute sql, , 129
Step 4: Let's get this user's details
sql = "EXEC dbo.GetUser '[email protected]'"
set rs = conn.execute(sql)
storedPassword = rs(1)
Step 5: Let's make credentials fail:
checkAccess storedPassword, "splunge"
Step 6: Now this one will work:
checkAccess storedPassword, "foobar"
Step 7: Here is the function where you handle checking and spit out the result, pass or fail.
sub checkAccess(storedPW, submittedPW)
if storedPW = MD5(submittedPW) then
Response.Write "Yay! It worked!"
' set session variables, redirect, etc
else
Response.Write "Boo! No access!"
end if
end sub
%>
Step 8: Clean-up:
DROP TABLE dbo.Users
DROP PROCEDURE dbo.AddUser, dbo.GetUser
Obviously, your application wouldn't flow exactly like this. You would have a different page for adding users than for checking credentials (so you wouldn't be creating a hash of a password and then verifying that it worked four lines later), and you would likely have a lot more information in the users table than e-mail address and password. But the above is a starting point to demonstrate the methodology. As you are testing the above, you will want to stop between each refresh and do the following:
TRUNCATE TABLE dbo.Users
A benefit of this approach is that you won't be using an undocumented function in SQL Server.
PWDENCRYPT() and PWDCOMPARE() are very tempting to use, but they are undocumented for a reason: they are unsupported, and could change or disappear with a new version of SQL Server, or even a service pack or hotfix. So if you build an application around this, it may come crumbling down at the worst possible time. Also, I'm not going to speak for the strength of the algorithm PWDENCRYPT uses, or the ability of a determined user to crack it.
Nonetheless, to be thorough, I will include a sample of how I would do this so that password comparison were handled inside the database, were these functions documented and supported (and weren't I so risk averse).
CREATE TABLE dbo.Users
(
email VARCHAR(255)
PRIMARY KEY CLUSTERED,
pass_word VARBINARY(255)
)
GO
CREATE PROCEDURE dbo.AddUser
@email VARCHAR(255),
@pass_word VARCHAR(16)
AS
BEGIN
SET NOCOUNT ON
INSERT dbo.Users
(
email,
pass_word
)
SELECT
@email,
PWDENCRYPT(@pass_word)
END
GO
CREATE PROCEDURE dbo.GetUser
@email VARCHAR(255),
@pass_word VARCHAR(16)
AS
BEGIN
SET NOCOUNT ON
SELECT email, HasAccess = PWDCOMPARE(@pass_word, pass_word)
FROM dbo.Users
WHERE email = @email
END
GO
Now the code to run a test (change out e-mail addresses to run multiple times, and don't forget to use a purposely false password to verify the failure "works"):
<%
set conn = CreateObject("ADODB.Connection")
conn.open "<connection string>"
submittedEmail = "[email protected]"
submittedPassword = "foobar"
Step 9: Let's create a hashed password
sql = "EXEC dbo.AddUser '" & submittedEmail & "', '" & submittedPassword & "'"
conn.execute sql, , 129
Step 10: Let's get this user's details
sql = "EXEC dbo.GetUser '" & submittedEmail & "', '" & submittedPassword & "'"
set rs = conn.execute(sql)
HasAccess = rs(1)
Step 11: Let's check if he has access
if CLng(HasAccess) = 1 then
Response.Write "Yay! It worked!"
' set session variables, redirect, etc
else
Response.Write "Boo! No access!"
end if
%>
Step 12: Clean-up:
DROP TABLE dbo.Users
DROP PROCEDURE dbo.AddUser, dbo.GetUser
You could do something similar using supported code by using CONVERT(VARBINARY), however this will only shield the password from the most casual users. You can convert to and from VARBINARY <-> VARCHAR without difficulty, so anyone with this knowledge would have free range over all the passwords in the table. At least in the above cases they will need to resort to a dictionary attack of some sort, and that can be prevented to some degree by both software and hardware (and can be pinpointed to a workstation if it is coming from within your company <G>).
In SQL Server you will be able to create CLR code, using any encryption algorithm you like, and handling it all inside the database, without using unsupported or undocumented functions.
Deny read access to the table
You could easily create a view or stored procedure that simulated the input mask functionality that Access provides. For example:
CREATE TABLE dbo.Users
(
email VARCHAR(255)
PRIMARY KEY CLUSTERED,
pass_word VARCHAR(16),
cc_number CHAR(16)
)
GO
INSERT dbo.Users(email, pass_word, cc_number)
SELECT '[email protected]', 'mypassword', '4111111111111111'
INSERT dbo.Users(email, pass_word, cc_number)
SELECT '[email protected]', 'x', '4111111111111111'
GO
CREATE VIEW dbo.UsersPublicInfo
AS
SELECT
email,
pass_word = '********',
cc_number = 'xxxxxxxxxxxx'+RIGHT(cc_number, 4)
FROM dbo.Users
GO
SELECT * FROM dbo.Users
SELECT * FROM dbo.UsersPublicInfo
GO
In order to prevent users from viewing the data, you can simply issue a DENY:
DENY ALL ON dbo.Users TO [username]
Now when this user tries to run the SELECT against dbo.Users listed above, they get the following error:
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Users', database 'pubs', owner 'dbo'.
They get similar errors if they try to UPDATE, INSERT or DELETE. In other words, if they want to see the data in the table, add new rows, or modify existing data, they will need to interface through *your* stored procedures. Of course, if they are an elevated user (e.g. a sysadmin in SQL Server) or can access the database as a domain admin, they can easily thwart your object-level permissions settings. Users you need to be concerned about should only be in the public, db_datareader and db_datawriter roles.
The benefit to this approach is that, because it is stored in plain text, the people who should have access to the data can easily view and update without having to use convoluted methods like 3rd party encryption or even data type conversion. Only you can determine how secure your data needs to be, relative to how easy you want your maintenance to be.
Posted on Utopian.io - Rewarding Open Source Contributors