Any way to pass operators <, <>, >= into sql-query?-CodePudding

I have such piece of program:

if self.current_condition == 'comparison':
comparison_command = '''SELECT * FROM {table} WHERE {pkey} < %s'''
cur.execute(sql.SQL(comparison_command).format(
                    table=sql.Identifier(self.current_table),
                    pkey=sql.Identifier(self.current_columns[0].text())
                ), 
                (self.comp_value, )
           )

What I want to do is write '<' in command in the same way as {table} and {pkey}, that means I want to pass operators into command from variable. Can I do it?

The face of the app looks like this A little bit more of code context. It's an app, that should get data from database by sql-request, that creates from interface. As you can see, there's a bit more operators than one to choose.

    def run_func(self):
    conn = None
    try:
        conn = psycopg2.connect(
            host='localhost',
            database='1rl',
            user='postgres',
            password=passwor)
        cur = conn.cursor()

        if self.current_condition == 'comparison':
            comparison_command = '''SELECT * FROM {table} WHERE {pkey} < %s'''
            cur.execute(sql.SQL(comparison_command).format(table=sql.Identifier(self.current_table),
                                                           pkey=sql.Identifier(self.current_columns[0].text())),
                                                            (self.comp_value, ))
            print(cur.fetchall())

    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()


def display(self, i):
    self.list_of_conditions.setCurrentIndex(i)
    self.current_condition = self.all_conditions[i]
    print(self.current_condition)



def comp_value_changed(self):
    self.comp_value = self.value.text()

def comp_on_selected(self):
    sender = self.sender()
    self.comp_selec = sender.text()

def comparison_fun(self):
    layout = QFormLayout()
    compars = QHBoxLayout()

    for i in self.all_comparisons:
        temp = QRadioButton(i)
        temp.toggled.connect(self.comp_on_selected)
        compars.addWidget(temp)

    layout.addRow(QLabel('Operators'), compars)

    self.value = QLineEdit()
    self.value.textChanged.connect(self.comp_value_changed)
    layout.addRow("Value", self.value)

    rune = QPushButton('Run')
    rune.clicked.connect(self.run_func)
    layout.addRow(rune)
    self.comparison.setLayout(layout)

CodePudding user response：

You can use string interpolation on comparison_command, use f-string notation, and double the existing braces to escape them:

comparison_command = f'SELECT * FROM {{table}} WHERE {{pkey}} {self.comp_selec} %s'
cur.execute(sql.SQL(comparison_command).format(
                    table=sql.Identifier(self.current_table),
                    pkey=sql.Identifier(self.current_columns[0].text())
                ), 
                (self.comp_value, )
           )

This assumes that self.comp_selec has the operator in the SQL syntax, i.e. it should be like "<", "<=", "=", ">=", ">", and not "==", "ge", "greater", ...etc.

I need to add the disclaimer about the risk of SQL injection. As I understand all of this runs on a local machine, a smart user could potentially tamper with the executable and make the SQL execute something harmful for the database or its security.

CodePudding user response：

Use sql.SQL to pass in the operator:

comparison_command = sql.SQL("SELECT * FROM {table} WHERE {pkey} {op} %s").\
    format(table=sql.Identifier("test"), pkey=sql.Identifier("id"), op=sql.SQL(">"))

print(comparison_command.as_string(con))                                                                                                                                                                                
SELECT * FROM "test" WHERE "id" > %s

for op in [">", "<", "="]:
    comparison_command = sql.SQL("SELECT * FROM {table} WHERE {pkey} {op} %s").\
    format(table=sql.Identifier("test"), pkey=sql.Identifier("id"), op=sql.SQL(op))
    print(comparison_command.as_string(con))

SELECT * FROM "test" WHERE "id" > %s
SELECT * FROM "test" WHERE "id" < %s
SELECT * FROM "test" WHERE "id" = %s