SQL why input string becomes 0?-CodePudding

I'm using python's psycopg2 to create a table and the insert SQL string look like this

create_table ='''CREATE TABLE Datetime_Response(
   rid BIGINT NOT NULL,
   qid BIGINT,
   roid BIGINT,
   rotext VARCHAR(20)
)'''

INSERT INTO Datetime_Response (rid, qid, roid, rotext)
VALUES
 (13020638659, 711799502, 4681912759, 07/21/2021)

But the output is weird. All the datetime string becomes 0. I tried both VARCHAR and TEST in the column rotext. They all show 0. I don't know what goes wrong.

(13020638659, 711799502, 4681912759, '0')

This is what the values looks like

values = list(entry.values())
        print(values)
['13020638659', '711799502', '4681912759', '07/21/2021']

And this is what the insert syntax look like

values_str = "(%s)" % (', '.join( values ))
sql_string = "INSERT INTO %s (%s)\nVALUES\n %s" % (
            table_name,
            ', '.join(columns),
            values_str
        )
        print(sql_string)
INSERT INTO GZ_Datetime_Response (rid, qid, roid, rotext)
VALUES
 (13020638659, 711799502, 4681912759, 07/21/2021)

CodePudding user response：

Do it like this. Let the connector fill in the properly quoted values:

sql_string = "INSERT INTO %s (%s)\nVALUES (%s)" % (
            table_name,
            ','.join(columns),
            ','.join(['?']*len(values))
        )
cur.execute( sql_string, values )

CodePudding user response：

Because 7 divided by 21 divided by 2021 is close to zero. / is integer division in Python 2 if both operands are an integer. Quote your value: 07/21/2021

Your query is open to SQL injection attacks by malicious users of your script!

values = list(entry.values())
        print(values)
['13020638659', '711799502', '4681912759', '07/21/2021']
values_str = "(%s)" % (', '.join( values ))
sql_string = "INSERT INTO %s (%s)\nVALUES\n %s" % (
            table_name,
            ', '.join(columns),
            values_str
        )
        print(sql_string)

When using your string values to format your string, you will end up with:

INSERT INTO GZ_Datetime_Response (rid, qid, roid, rotext) VALUES (13020638659, 711799502, 4681912759, 07/21/2021)

which does not contain any quotation marks. Use prepared statements to avoid problems caused by wrong types and to stop hackers from misusing your application/database.

If, for whatever reason, you cannot use prepared statements, make sure the string ends up as string in your final statement:

values = ['13020638659', '711799502', '4681912759', "'07/21/2021'"]
or INSERT INTO GZ_Datetime_Response (rid, qid, roid, rotext) VALUES (%s, %s, %s, '%s)