How to use ColdFusion to check database's value?

Question

Now, I'm using ColdFusion to add user:

<cfquery name="adduser" datasource="music" dbtype="ODBC">

    insert into register values('#username#','#password#')
</cfquery>

If user add same username, the web page will tell the user that the name has been taken. Can you tell me how to achieve this? I just don't know how to write if statement.

Sorry, the cold should be: insert into register values('#username#','#password#') — iTvX, Apr 26 '19 at 14:32
You need to select the count of usernames where username=#username#, but make sure you use `` for safety. Right now you're vulnerable to SQL injection — Sterling Archer, Apr 26 '19 at 14:34
Please clarify `I just don't know how to write if statement. `. Does this mean you don't know how to write if statements at all, or that you don't know what to do in this situation? — Dan Bracuk, Apr 26 '19 at 15:10
I don’t know what to do in this situation. Can you help me with this? My question is if user add same username, the web page will tell the user that the name has been taken. — iTvX, Apr 26 '19 at 16:12
So it sound like you what to SELECT if a similar user exists, and return a message if the user does. I suppose you need one query to SELECT the user, and another to do the insert — James A Mohler, Apr 26 '19 at 16:45
找到了 insert into register values('#username#','#password#') 已经创建 — iTvX, Apr 26 '19 at 17:00
That won't work if two sessions are trying to insert the same data at the same time. And it can be susceptible to blocking. You can trap the error that SQL returns if it violates that constraint, but then you chase trying to add a username. If this is a requirement in the database, then you also need to have an explicit constraint in the db. Or there are tons of other ways to verify a username before insert. — Shawn, Apr 26 '19 at 18:01
Of much more concern to me though is that 1) you definitely want to use queryparams to protect against SQL injection and 2) #password# seems to indicate that you are storing plaintext passwords. This is very, very bad idea. Even if it's a simple website. Nobody should ever be able to know a user's password except that user. There are tons of resources for secure password storage and validation. https://www.owasp.org/index.php/Password_Storage_Cheat_Sheet — Shawn, Apr 26 '19 at 18:04
Good point about sessions. Nothing to do with the question, but `dbtype=odbc` is deprecated in recent versions. — SOS, Apr 26 '19 at 22:43

score 1 · Answer 1 · edited Apr 29 '19 at 05:25

Query all users with the same username like the user you are about to save (for performance purposes, you maximally need 1 row to prove it's there already):

<cfquery name="checkuseralreadythere" datasource="music">
   SELECT username 
   FROM   register 
   WHERE  username = <cfqueryparam cfsqltype="cf_sql_varchar" 
                        value="#username#"> 
  LIMIT 1
</cfquery>

Program logic to check results

<cfif checkuseralreadythere.recordcount GTE 1>
   <!--- user is already there --->
   Username is already taken. please enter an other username.
   <cflocation url="samepage.cfm?err=1">
    ...do more stuff...
<cfelse>
   <!--- user is not there yet --->
   <cfquery name="adduser" datasource="music">
      INSERT INTO register 
      VALUES (
           <cfqueryparam cfsqltype="cf_sql_varchar" 
                        value="#username#"> 
           , <cfqueryparam cfsqltype="cf_sql_varchar" 
                        value="#password#">
       )
   </cfquery>
   ...do more stuff...
</cfif>

As mentioned in the [main comments](https://stackoverflow.com/questions/55869775/how-to-use-coldfusion-to-check-databases-value#comment98405481_55869775), the problem with that approach is that it isn't thread safe. It won't prevent duplicate usernames if multiple requests hit the code block at the same time (which *will* happen sooner or later). — SOS, Apr 28 '19 at 04:20

lavrys · Answer 2 · 2019-05-02T00:00:56.433

The robust way - with locks. This also ensures that you can't have two people submit the same username "at the same time" (if they are using the same website?) and you end up inserting both.

<cfquery name="adduser">
    INSERT 
    INTO Register(Username,Password)
    OUTPUT INSERTED.Username
    SELECT 
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#Username#" />, 
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#Password#" />
    WHERE <cfqueryparam cfsqltype="cf_sql_varchar" value="#Username#" /> NOT IN (
        SELECT Username
        FROM Register
        WITH(ROWLOCK, UPDLOCK, SERIALIZABLE)
        WHERE Username = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Userame#" />
   )
</cfquery> 

<cfif adduser.RecordCount EQ 1>
    <!--- success ---->
<cfelse>
    <!--- failure ---->
</cfif>

As the insert could potentially fail for different reasons, if you really wish to be sure, in the failure "cfelse" you can add a query to check if the username existed.

You should also consider making that DB field UNIQUE, so the INSERT will fail - but in that case you have to TRY-CATCH the query to catch the failures.

How to use ColdFusion to check database's value?

2 Answers2