Human-guided, AI-assisted coding for secure SQL table development- Proof of Concept
There is a lot of talk on LinkedIn and in the media about how AI is changing the world. I believe this to be true, but think that the panic surrounding how AI will replace humans is unfounded. AI is a powerful tool, but useless without the guiding hand of informed humans.
AI is powerful because it takes the labor out of code development. However, it's not a replacement for developers because they have the knowledge on how to implement, modify, and troubleshoot code solutions based on the specific need case of an organization.
That being said, I've been working on a method for developing a secure SQL table to store usernames, passwords, etc. by leveraging my knowledge of #cybersecurity, #sql, #ai, #logic, and #learninganddevelopment principles. The cool thing about this method is that it only requires a little knowledge in each area.
I have a transcript that shows the steps I took to get to the output below and how modifying the inputs improved the output over time. If there is interest, I can publish the transcript showing the process.
The objective of this post is to get some feedback on the efficiency and security of the #code (DB: postgreSQL, formatted w/Notepad++).
Please share your feedback, knowledge, and tips in the comments!
CREATE TABLE secure_use
(
user_id SERIAL PRIMARY KEY
, username VARCHAR(50) NOT NULL UNIQUE
, salt VARCHAR(128) NOT NULL
, password_hash VARCHAR(128) NOT NULL
, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
, updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
)
;
-- Grant SELECT permission to an application user
GRANT
SELECT
ON
TABLE secure_user TO my_application_user
;
-- Generate a random salt value for each new user and hash their password with bcrypt
CREATE OR REPLACE FUNCTION create_user
(
in_username VARCHAR(50)
, in_password VARCHAR(255)
) RETURNS VOID
AS
$$
DECLARE
salt_value VARCHAR(128);
BEGIN
-- Generate a random salt value
SELECT
gen_salt('bf', 10)
INTO
salt_value
;
-- Hash the password with the salt value using bcrypt
INSERT INTO secure_user
(username
, salt
, password_hash
)
VALUES
(in_username
, salt_value
, crypt(in_password, salt_value)
)
;
END;
$$ LANGUAGE plpgsql;
-- Verify a user's password by checking the hashed value
CREATE OR REPLACE FUNCTION verify_password
(
in_username VARCHAR(50)
, in_password VARCHAR(255)
) RETURNS BOOLEAN
AS
$$
DECLARE
stored_password_hash VARCHAR(128);
BEGIN
-- Get the stored password hash and salt for the specified username
SELECT
password_hash
FROM
secure_user
WHERE
username = in_username
INTO
stored_password_hash
;
-- Check if the password hash matches the stored value
RETURN stored_password_hash = crypt(in_password, stored_password_hash);
END;
$$ LANGUAGE plpgsql;
-- Use prepared statements to prevent SQL injection attacks
PREPARE insert_user (VARCHAR(50), VARCHAR(255)) AS
INSERT INTO secure_user
(username
, salt
, password_hash
)
VALUES
($1
, $2
, $3
)
;
PREPARE select_user (VARCHAR(50)) AS
SELECT
password_hash
FROM
secure_user
WHERE
username = $1
;
-- Use parameterized queries to protect against SQL injection and keep sensitive information separate from SQL code
CREATE OR REPLACE FUNCTION create_user
(
in_username VARCHAR(50)
, in_password VARCHAR(255)
) RETURNS VOID
AS
$$
DECLARE
salt_value VARCHAR(128);
BEGIN
-- Generate a random salt value
SELECT
gen_salt('bf', 10)
INTO
salt_value
;
-- Hash the password with the salt value using bcrypt
EXECUTE insert_user(in_username, salt_value, crypt(in_password, salt_value));
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION verify_password
(
in_username VARCHAR(50)
, in_password VARCHAR(255)
) RETURNS BOOLEAN
AS
$$
DECLARE
stored_password_hash VARCHAR(128);
BEGIN
-- Get the stored password hash and salt for the specified username
EXECUTE select_user(in_username) INTO stored_password_hash;
-- Check if the password hash matches the stored value
RETURN stored_password_hash = crypt(in_password, stored_password_hash);
END;
$$ LANGUAGE plpgsql;r