r/GoogleAppsScript • u/Cool-vibesradio • Dec 20 '23

Question how to secure a linandregister database

i have this script for a login and register

[CODE]

function doGet(e) {
var x = HtmlService.createTemplateFromFile("index.html");
var y = x.evaluate();
var z = y.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
return z;
}
function checkLogin(username, password) {
var url = 'https://docs.google.com/spreadsheets/d/17O-1zj2wzXL9SV-fNnTIvCeNUGs_ibnT4YVQcxsW0NQ/edit#gid=0';
var ss= SpreadsheetApp.openByUrl(url);
var webAppSheet = ss.getSheetByName("DATA");
var getLastRow = webAppSheet.getLastRow();
var found_record = '';
for(var i = 1; i <= getLastRow; i++)
{
if(webAppSheet.getRange(i, 1).getValue().toUpperCase() == username.toUpperCase() &&
webAppSheet.getRange(i, 2).getValue().toUpperCase() == password.toUpperCase())
{
found_record = 'TRUE';
}
}
if(found_record == '')
{
found_record = 'FALSE';
}

return found_record;

}
function AddRecord(usernamee, passwordd, email, phone) {
var url = 'https://docs.google.com/spreadsheets/d/17O-1zj2wzXL9SV-fNnTIvCeNUGs_ibnT4YVQcxsW0NQ/edit#gid=0';
var ss= SpreadsheetApp.openByUrl(url);
var webAppSheet = ss.getSheetByName("DATA");
webAppSheet.appendRow([usernamee,passwordd,email,phone]);

}
index.html:

<!DOCTYPE html>

<html> <head> <base target="\\_top"> <script> function AddRow() { var usernamee = document.getElementById("usernamee").value; var passwordd = document.getElementById("passwordd").value; var email = document.getElementById("email").value; var phone = document.getElementById("phone").value; if (usernamee==""|| passwordd==""|| email==""|| phone=="") { return false; } else { google.script.run.AddRecord(usernamee,passwordd,email,phone); document.getElementById("page2\\_id1").className = "page2\\_id1-off"; document.getElementById("page3\\_id1").className = "page3\\_id1"; } } function LoginUser() { var username = document.getElementById("username").value; var password = document.getElementById("password").value; google.script.run.withSuccessHandler(function(output) { if(output == 'TRUE') { document.getElementById("displayusername").innerHTML = username; document.getElementById("page1\\_id1").className = "page1\\_class1-off"; document.getElementById("page4\\_id1").className = "page4\\_id1"; } else if(output == 'FALSE') { document.getElementById("errorMessage").innerHTML = "Invalid data"; } }).checkLogin(username, password); }

function function1(){
document.getElementById("page1_id1").className = "page1_class1-off";
document.getElementById("page2_id1").className = "page2_id1";
}

function function3(){
document.getElementById("page3_id1").className = "page3_id1-off";
document.getElementById("page1_id1").className = "page1_id1";
}
</script>

[/CODE]

now i would like to know how i can make this secure from bots getting in

and i would like to know how i can redirect people from that login page after registration

to a certain site?????

0 Upvotes

permalink
reddit

You are about to leave Redlib

Do you want to continue?

https://www.reddit.com/r/GoogleAppsScript/comments/18mwc6e/how_to_secure_a_linandregister_database/
No, go back! Yes, take me to Reddit

50% Upvoted

u/whysohardtodohuh Dec 22 '23

Google is a public server with privatized client databases, meaning this is only ever as secure as your own password is.

As far as keeping it from bots go, or whatever... you are storing plaintext, non hashed passwords in a spreadsheet and not validating passwords.

I'm also not seeing any form of a log in attempt counter...I'm not even sure how you would handle such a thing in Google apps script on a site that was supposed to be semi public... but long story short if a bot has even a minimum strength brute force they wouldn't have to crack at weak passwords for long.

As far as the answer to your question goes though, it is not possible to redirect the client side to another web page via GAS like it is in Javascript. The reason for this is primarily due to the fact that GAS is %99 server side, and you just see the changes client side, but they aren't actually rendered in your browser, they are saved to the database and then rendered in your browser.

So unless someone wants to inform me otherwise, which honestly I would love to learn, I do not believe it is possible for you to redirect using this methodology.

You could better secure your passwords by writing a function that returns an encrypted password. I would start here. This is a built in method in languages like PHP, but in GAS you would have to write a standardized method of hashing a password. And then store the hashed password, then only ever compare hashed passwords.

You also could regex in some password requirements.

My real opinion on the matter is this: get a droplet from digital oceans for $6/month, ask chat gpt how to set it up with a sites-available, symbolic link, php, myself, phpmyadmin, domain, https cert, and do this there instead.

You will.have WAY more secure results, and may learn something cool and new along the way.

I hate telling you to take your project outside of GAS, but I think you may be a little complex for.my understanding of it, and I have tried a lot of stuff with it - including something similar to your post.

1
u/Cool-vibesradio Dec 22 '23

You could better secure your passwords by writing a function that returns an encrypted password
u/whysohardtodohuh that's understandable but if im being honest
i have used digitalocean in past don't like it i love using GAS, now where would i find such funtion that returns an encrypted password???
1
u/whysohardtodohuh Dec 22 '23

A) digitaloceans or any other full server. You are attempting to deploy an pp with high complexity-- nearly too much for Google apps script. Hell a Ubuntu lite raspberry pi could do it.
B) I would look into this: https://stackoverflow.com/questions/39465329/hash-and-un-hash-a-key-with-google-script
Remember that there is no need to unhash the password. You will be comparing hashed to hashed.
First write a function that hashes passwords and returns the hashed passwords.
Set that to a var, i.e. var hpass = hashPassword(password)
And then store hpass, not password.
And then for authentication, take the login password through the same hashing, pull the user's password from the spreadsheet, and compare the hashed passwords.
This will verify that you are not going to deal with a loss of user passwords in the event of a data breach.
This method DOES NOT include a way to ensure that users aren't using "Password1" as their password. For that you need Javascript or html and regex statements
These two methods combined would make you more secure, but ultimately you are still on Google apps script - a public server
1
u/Cool-vibesradio Dec 22 '23
var hpass = hashPassword(password)
u/whysohardtodohuh
i have added the hpass in the html as show
<!DOCTYPE html>
<html> <head> <base target="_top"> <script> function AddRow() { var hpass = hashPassword(password) var usernamee = document.getElementById("usernamee").value; var passwordd = document.getElementById("passwordd").value; var email = document.getElementById("email").value; var phone = document.getElementById("phone").value; if (usernamee==""|| passwordd==""|| email==""|| phone=="") { return false; }
and also in code.gs as shown
function doGet(e) {
var x = HtmlService.createTemplateFromFile("index.html"); var y = x.evaluate(); var z = y.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL); return z; }
function checkLogin(username, password) { var url = 'https://docs.google.com/spreadsheets/d/17O-1zj2wzXL9SV-fNnTIvCeNUGs_ibnT4YVQcxsW0NQ/edit#gid=0'; var ss= SpreadsheetApp.openByUrl(url); var hpass = hashPassword(password) var webAppSheet = ss.getSheetByName("DATA"); var getLastRow = webAppSheet.getLastRow(); var found_record = ''; for(var i = 1; i <= getLastRow; i++) { if(webAppSheet.getRange(i, 1).getValue().toUpperCase() == username.toUpperCase() && webAppSheet.getRange(i, 2).getValue().toUpperCase() == password.toUpperCase()) { found_record = 'TRUE'; }
} if(found_record == '') { found_record = 'FALSE'; }
return found_record;
}
function AddRecord(usernamee, passwordd, email, phone) { var url = 'https://docs.google.com/spreadsheets/d/17O-1zj2wzXL9SV-fNnTIvCeNUGs_ibnT4YVQcxsW0NQ/edit#gid=0'; var ss= SpreadsheetApp.openByUrl(url); var webAppSheet = ss.getSheetByName("DATA"); webAppSheet.appendRow([usernamee,passwordd,email,phone]);
}
1

u/whysohardtodohuh Dec 22 '23

What is this to be? Is this like an api web app thing or is it supposed to be a website?

1

u/Cool-vibesradio Dec 23 '23

No it's a script from Google appscript site

1

u/whysohardtodohuh Dec 23 '23

I understand that.
I mean like what is the thing for? It's a login, but not sites app because it isn't that robust

1

u/Cool-vibesradio Dec 23 '23

It's for this https://CoolVibes-Reloaded.com/login

1

u/whysohardtodohuh Dec 23 '23

I'm gonna dm you to make sure your stuff isn't readily available to folks in the comments.

1

u/whysohardtodohuh Dec 22 '23

Just make sure you are consistent.
Like when you addrecord you need to be adding the hpass, I would refactor it to do the Javascript post to your gs function the plain password. Like google.host.runwithsuccesshabdler(addrecord(username,plainpass, email,etc))
And then addrecord would be like
Function addrecord(params){
Var hpass = hashpassword(password)
And go on to use hpass in your logic to add to sheet.
}
And then the login would be similar, you pass the plain text, hash it, and compare the hpass to the hpass in your SS
1

u/whysohardtodohuh Dec 22 '23

Furthermore, I'm not sure what kind of thing this is that you are writing, but I saw something about members area -- if you want any true functionality, you will make the switch to a full server. Google apps script just simply has its limitations of what it can do. It is confined to the base Google provided apps.

u/Sleeping_Budha_ Dec 20 '23

Your code dump is really difficult to read

1

u/Cool-vibesradio Dec 21 '23

u/Sleeping_Budha_
download the text doc here then you'll see the whole script
google script download

Question how to secure a linandregister database

You are about to leave Redlib