Home > Software engineering >  No operator matches the given name and argument types. You might need to add explicit type casts
No operator matches the given name and argument types. You might need to add explicit type casts

Time:11-09

I am using PostgreSQL for storing username and their corresponding salted and hashed password but it is continuously giving me this error.

LINE 1: ...egister where USERNAME = 'siddharth' and PASSWORD = '\x24326...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

My Table Name is hassle_free_register and its structure is as follows as :

                                       Table "public.hassle_free_register"
  Column  |          Type          | Collation | Nullable |                        Default
---------- ------------------------ ----------- ---------- -------------------------------------------------------
 user_id  | integer                |           | not null | nextval('hassle_free_register_user_id_seq'::regclass)
 username | character varying(255) |           | not null |
 password | character varying(255) |           | not null |
Indexes:
    "hassle_free_register_pkey" PRIMARY KEY, btree (user_id)
    "hassle_free_register_username_key" UNIQUE CONSTRAINT, btree (username)

CODE for registering the user :-

   @app.route('/register' ,methods =['POST'])
def register():
   try:
      NAME = request.form['USER_NAME']
      PASSWORD = request.form['USER_PASSWORD']
      if(len(NAME)==0):
         return jsonify({"message" :"USERNAME CANNOT BE EMPTY"}),400
      if(len(PASSWORD)<=8):
         return jsonify({"message" :"PASSWORD LENGTH TOO SHORT"}),400
      if(len(PASSWORD)>=30):
         return jsonify({"message" :"PASSWORD LENGTH TOO LONG"}),400
      HASHEDPASS = bcrypt.hashpw(PASSWORD.encode('utf-8'),bcrypt.gensalt())    
      mycursor.execute("insert into hassle_free_register (USERNAME,PASSWORD) values(%s, %s);",(NAME,HASHEDPASS)) 

      #ERROR IS OCCURING IN THIS LINE
      mycursor.execute("select USER_ID from Hassle_Free_Register where USERNAME = %s and PASSWORD = %s;",(NAME,HASHEDPASS)) 

      data = mycursor.fetchone()
      print(data)
      mycursor.execute("create table {TABLENAME} (PASSWORD_ID int SERIAL NOT NULL PRIMARY KEY,APP_NAME varchar(255) NOT NULL, APP_USERNAME varchar(255) NOT NULL , APP_PASSWORD varchar(255) NOT NULL);".format(TABLENAME = NAME   "_"   str(data[0])))
      mydb.commit()
      return jsonify("REGISTERED SUCCESSFULLY") 
   except TypeError as error:
      print(error)
      return jsonify({"message":str(error)}),403
   except ValueError as error:
      print(error)
      return jsonify({"message":str(error)}),403
   except psycopg2.Error as error:
      print(error)
      return jsonify({"message":str(error)}),403

(EDITED AFTER COMMENTS) I am new to Python and postgrSQL. Please Help !

I ran the query which was giving the error in the SQL shell (psql) but it ran successfully their.

QUERY :-

hassle_free=# select USER_ID from hassle_free_register where USERNAME = 'siddharth' and PASSWORD = '\x24326224313224587174764d532e6265334d6654354e47514a6d73674f6d72327a75723966747a42542e5a2e4f48374e74446d6e76355353752e7461';

RESULT :-

 user_id
---------
      39
(1 row)

Please Help.

CodePudding user response:

To be clear what is happening:

create table pwd_test(id int, password varchar);

import psycopg2
import bcrypt 
pwd = bcrypt.hashpw('test_pwd'.encode('utf-8'),bcrypt.gensalt()) 

pwd
b'$2b$12$DTSJSsuuhwgyMdSOqLmb0.4RAk.smxQERas/i7WcR3NKTPtLQfoPK'
# Note the b'
# From here [Binary adaptation](https://www.psycopg.org/docs/usage.html#adapt-binary)
# This gets converted to bytea

cur.execute('insert into pwd_test values(%s,%s)', [2, pwd])
-- On the INSERT it gets cast to a varchar
select * from pwd_test where id = 2;
 id |                                                          password                                                          
---- ----------------------------------------------------------------------------------------------------------------------------
  2 | \x243262243132244454534a53737575687767794d64534f714c6d62302e3452416b2e736d7851455261732f6937576352334e4b5450744c51666f504b

# When you do the comparison you are comparing the varchar value in the table to the bytea type that is pwd.

cur.execute('select * from pwd_test where password = %s', [pwd])                                                                                                          
---------------------------------------------------------------------------
UndefinedFunction                         Traceback (most recent call last)
<ipython-input-15-e2bde44ff261> in <module>
----> 1 cur.execute('select * from pwd_test where password = %s', [pwd])

UndefinedFunction: operator does not exist: character varying = bytea
LINE 1: select * from pwd_test where password = '\x24326224313224445...
                                              ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

# You need to cast the varchar to bytea
cur.execute('select * from pwd_test where password::bytea = %s', [pwd]) 

cur.fetchone()
 id |                                                          password                                                          
---- ----------------------------------------------------------------------------------------------------------------------------
  2 | \x243262243132244454534a53737575687767794d64534f714c6d62302e3452416b2e736d7851455261732f6937576352334e4b5450744c51666f504b

So your options are do the cast of password to bytea in the query or change the column type of password to bytea.

  • Related