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
.