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

Time:06-21

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
  • Related