User authentication via username and password using external database

Hello appery.io support,
I (and other customers) would like to know if it's really possible to create a method which allows me to implement a simple username/password authentication (no registration) using my own external MS SQL Server database.
After reading a lot of tutorials, documents, walkthroughs and watching videos, my knowledge on this topic hasn't increased so far.
I successfully created an SQL query which takes @username and @password as parameters and gives user information if both parameters are in the database (let's say it's a "fake" login):
"SELECT * from users
WHERE username = :username AND password = :password AND userBlocked IS NULL"
Now, after this step, how can I link this function created through the "Custom REST API" to two inputBoxed and a loginButton so if the login happens a new page is opened with user information and if the login doesn't happen a popup appears with a "Invalid credentials" message?
I'm stuck at this step so I can't really proceed to commit to a subscription.
I would like to add @Jeffry Reed because he would be interested in this topic as well plus he's having my same problem.

Thank you so much
Andrew
1 person has
this problem
+1
Reply
  • You should configure the API Express service to return a response code based on the query result. It should be 200 (success) when a login is successful and maybe 404 (not found) when a user is not found.

    Once you finished and tested the API Express service (you want to test and make sure the service works before using it in the app), you will import inside the App Builder. A video on this page will show how to do that: https://docs.appery.io/docs/apiintegr....

    Once an API Express service is imported and added to a page, you will create success/failure callbacks. When the service returns 200 - then you navigate to another page. When a service returns 404 -- then you show an error message.

    The Server Code YouTube playlist has a number of videos on how to import, map and invoke services from app. This particular video (latest recorded) shows how to build an app with API Express service: https://www.youtube.com/watch?list=PL...
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • Hello Max,
    thank you very much for your help! I finally made some steps forward:
    I edited the "URI" in the "Start" block of the "Custom REST API" to add {username} and {password} as parameters and mapped :username and :password to {username} and {password}.
    Now, do I need to add an "if" block or a "script" block to check if username and password are right (200) or wrong (403)?
    I also made a fast app which takes username and password as input and gives name of the customer if the "fake" login happened! One small step forward!
    Another thing, I open a page with "Apperyio.navigateTo("page_name", {});", right?

    Thank you VERY much Max!

    Andrew
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • Hi Max, it is starting to make sense, possibly Andrew has fully understood, but I am kind of stuck where Andrew left off

    "Now, do I need to add an "if" block or a "script" block to check if username and password are right (200) or wrong (403)?"

    I think what he is asking, what I am trying to understand next is how to go about "configure the API Express service to return a response code based on the query result"?

    Start Block


    SQL Block


    Generate Service Response Success


    Generate Service Response Fail

    Thank you for guidance
    Jeff
    • Thank you very much Jeffry for the detailed images!
      You couldn't explain better what I was trying to say:
      how do we recognise a login happened with success i.e. username and password match (200) vs. an unauthorized login attempt i.e. wrong username and/or password (403).
      I think we're close to succesfully making our login page and going forward!

      Thank you Max for the priceless help and Jeffry for sorting out this "issue" with me,
      Andrew
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • Let's say this is the flow: Start > SQL > Script > End.

    The SQL component returns the found user or nothing ([]).

    Inside the Script component you can place the following logic:

    if (BODY.length > 0)
    result = {"status": "User found", "code": 200}
    else
    result = {"status": "User not found","code": 404}


    For the End component, set Response to BODY and HTTP code to BODY.code.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • Max, it works!

    I can't thank you enough for taking the time to help us figure this out. I know we are new to this and maybe we should have been able to figure it out on our own, we are learning though and I think having this documented creates a valuable resource for appery and the community.

    Think of the possibilities once we nail this down
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited kidding, amused, unsure, silly indifferent, undecided, unconcerned sad, anxious, confused, frustrated

  • As I am working through this I have now run into addition confusion. Andrew, Max, or anybody interested maybe you can help me figure this out.

    Testing in API Express seems to work just fine, however, testing in the app is causing some confusion...

    In API Express Test I get the following

    HTTP Response Code: 404 Not Found
    {
    "status": "User not found",
    "code": 404
    }

    HTTP Response Code: 200 Success
    {
    "status": "User found",
    "code": 200
    }

    However, in the app API Express Service Test I get the following

    Using Invalid username/password
    {
    "status": 404,
    "uri": "https://api.appery.io/rest/1/apiexpre...",
    "response": {
    "status": "User not found",
    "code": 404
    }
    }

    Using Valid username/password
    {
    "status": "User found",
    "code": 200
    }

    Because they are two different response formats, the Import as Response results in two different mappings.

    I created a simple simple page with txtUsername and txtPassword, a button that invokes the authenticate service, then finally a couple labels that are mapped to a model with Response object that has two variables (status and code).

    If I import the response based on Valid username/password, and then enter a correct username/password the labels display the status and code, if username/password are blank or incorrect nothing happens.

    If import as mapping based on Invalid username/password, again if the username/password are blank or incorrect nothing happens, if they are correct the labels display "undefined"
    • view 5 more comments
    • The communication between the app the server is secured, so in general, this is a secure way to log in.

      For paradigm - are you referring to the login flow in this example?
    • An update on a different response. The different response only happens when testing the service inside the App Builder. When the service is invoked in the app the correct response is returned:

      {
      "status": "User found",
      "code": 200
      }

      This is only an issue when testing the API (Test tab in Service editor) - we will fix it.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • Hello Andrew, Max, and appery.io community,

    Andrew, have you been following this? What are your thoughts?

    I realize this is a rather long post, possibly mostly for myself thinking this through.

    With regards to the paradigm is spoke of... to step back again and look at the big picture of what we are seeking to accomplish – a model of integrating with existing enterprise systems.

    While we have certainly made significant progress, there are still many pieces that need to be incorporated into the model to achieve the objective. While I have a specific project that I will eventually be working on, the way I am thinking about this is not necessarily developing a model specific to the app I have been tasked to create, but rather a generalized model that embodies best practices of developing mobile applications within the appery.io platform that integrate with existing systems. While LDAP is widely used, I think that probably a more common method is to store the credentials in a RDMS with the password encrypted (e.g. hashed and salted).

    So I guess my question is do you think the model we are developing is a good one? The model we are moving forward with will predicate future decisions in how the other pieces are implemented.

    I think the term Login Service may not be the right term, at this point I think it may be more accurate to say validation. In order to have a functional Login Service we need some additional pieces.

    For a number of reasons I am not starting out by developing the mobile app I will eventually create. At this point I am developing an app that integrate with the Sakila sample database https://dev.mysql.com/doc/sakila/en/ (the world really need a mobile app for DVD rental stores, right?), But seriously though, the schema pretty much exemplifies your typical relational database.


    To recap where we are at...

    1. DONE
    - Create a login service which accepts username/password which are stored in an external database. The login service would consist of a SQL component (with SQL query to make a request to the external database).

    2. TO DO
    - If credentials are correct then generate a user session token (using your backend/database). You can save the session token in Appery.io database.

    3. TO DO
    - You can check this session token before any secured service invocation.

    At this point I am moving on to understand the session token generation piece. Looking at the Sakila schema, I think we need to generate the session token and store it in the Appery.io database along with the staff_id. What I don’t understand is why one would need to “generate a user session token (using your backend/database)”. Why not simply generate the session token with server code?
    • view 3 more comments
    • Hello Andrew,

      I deleted the sample app I used to test the service, which I simply displayed the status in a label "User found" or "User not found", it did work though. I will recreate and post the details later.

      This matter is on my mind today and planned on putting together a post describing my thoughts on how to proceed, which I am not sure of.

      I was thinking along these lines...

      "The SQL component runs a custom SQL query to check if a user exists in a database."

      "The Script component adds custom logic to check what the SQL component returned and defines the response (user found or user not found)."

      At this point, if the response is "user found" capture the user_id and store it in a storage variable which can be used as a parameter. This is not what appery suggested as the flow (see previous posts), so I am not sure if this is viable solution.

      Some other thoughts... we are not the first to desire the functionality of authenticating on username/password store in external RDMS. Every discussion here in appery forums, as well as external sources (DreamFactory forums have some good discussions on the topic) - no one has found a solution, at least not a full fledged solution from beginning to end. Based on this observation I am considering that it may not really be possible and that the real solution is to implement OAuth or LDAP/Active Directory, which is what all the MBaaS providers support. Appery.io engineers are very smart people as are the engineers at DreamFactory. If they can not explain how to do it, possibly it's just not viable.

      Although, Max did post the video How to Build a Login Service with the SQL and Script Components, which does indeed check what the SQL component returned and defines the response username/password found or username/password not found... the question is how to develop the addition pieces in a way that is secure.
    • Hey Jeffry, sorry for not replying in a while but I think I lost all my hopes in this.
      What's going on for you?
      I made this little flowchart which can maybe explain better the whole process:

      get icon

      get icon

      Regards,
      Andrew
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • Hello Andrew,

    I have made it as far as validating that the username/password are correct, if invalid display an error, if correct store session variables and navigate to page.

    I have not dealt with the session token piece. I decided to take a break from that aspect and work on other aspects of the app development. I have spent a lot of time on authentication without a lot to show for it so I figured I would work on other aspects for a bit.

    If you haven't gotten as far, I will be glad to share in detail.

    Jeff
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • 1
    How to Build a Login Service to External SQL Database

    Create a new API Express database connection

    Create a new API Express project

    Create a new API Express Custom REST API service

    Create Input REQUEST QUERY PARAMETERS email and password

    Add SQL COMPONENT with the following query

    SELECT user_id, email, password from users
    WHERE email = :email AND password = :password

    Add SCRIPT COMPONENT with the following script

    var obj = BODY
    if (BODY.length > 0)
    result = {"status": "Success", "code": 200, "user_id": obj[0].user_id}
    else
    result = {"status": "Failure","code": 404, "user_id": "0"}

    Set the RESPONSE BODY HTTP status code to BODY.code

    In the app, create new API Express Service and Import the API Express service just created, I renamed to UserValidation

    Create a new storage viable called user_id

    Create a new page called loginPage and set as the start page

    In the loginPage add the following controls: INPUT txtEmail, INPUT txtPassword, BUTTON btnSignIn, LABEL lblLoginError, LABEL lblUserId. Set the LABEL lblUserId to not visible.

    In the loginPage DATA tab, add the UserValidation service, I named it userValidationService

    Open the before send event mapping and map the txtEmail and txtPassword controls to the service request query parameters, save and return.

    Open the success event mapping and map the service response user_id to the user_id storage variable, save and return.

    Add another success event navigate to page of your choice

    Add an error event set property where you set the lblLoginError to Login Failed

    In page design, create btnSignIn click event invoke service userValidationService

    That’s it! Now we can use the user_id storage variable as an input query parameter for other API Express Services. However, please note that this flow does not incorporate the session token piece of authentication. This has allowed me to move forward with development so I can learn other aspect.
    It would be great to receive feedback and suggestions
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • Jeffry Reed - THANK YOU so much for posting this! I've looked at numerous appery.io video tutorials and text instructions but none of them showed how to validate users against an external relational cloud database. I followed your instructions carefully (and modified them slightly for my situation) and it worked the very first time! How often does THAT happen?!

    So - THANKS AGAIN for being a giant upon whose shoulders I can stand!
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. indifferent, undecided, unconcerned happy, confident, thankful, excited kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • Perhaps this post is still live. I am attempting to create an app with an external database for authentication. Does any one has a proven guide to implement this. I am developing a JQuery app.

    Thanks

    Mike
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited sad, anxious, confused, frustrated kidding, amused, unsure, silly indifferent, undecided, unconcerned