Query between char and varchar returns nothing-CodePudding

I have the following query:

datacollector_cursor.execute(f"SELECT CNP FROM tblclient WHERE CNP='{str(row[0])}'")
aux = datacollector_cursor.fetchall()

Where CNP is a varchar variable and row[0] is char type. The issue is that this query never returns anything even if the two should be equal. I've manually replaced row[0] with an actual variable that meets the requirements and it outputs what I need. Why isn't this comparison correct?

CodePudding user response：

CHAR() are always filled with spaces up to their declared size in the table DDL, so 'ABC' as varchar2(6) != 'ABC ' as char(6).

CodePudding user response：

Don't build queries using string concatenation (or template strings) as that is how you introduce SQL injection vulnerabilities into your code.

Instead, use bind variables. If you are using cx_Oracle:

sql = "SELECT CNP FROM tblclient WHERE CNP=:value"
datacollector_cursor.execute(sql, [str(row[0])])
aux = datacollector_cursor.fetchall()

If you are having problems with blank padding semantics then you can use TRIM:

sql = "SELECT CNP FROM tblclient WHERE TRIM(RIGHT ' ' FROM CNP)=TRIM(RIGHT ' ' FROM :value)"
datacollector_cursor.execute(sql, [str(row[0])])
aux = datacollector_cursor.fetchall()