Python MySQL: Select with default value

Question

I have a database created from

CREATE TABLE `ip` (
  `idip` int(11) NOT NULL AUTO_INCREMENT,
  `ip` decimal(45,0) DEFAULT NULL,
  `mask` int(11) DEFAULT NULL,
  PRIMARY KEY (`idip`),
  UNIQUE KEY `ip_UNIQUE` (`ip`)
)

And I've made some insertions into this table

But when I try to execute on python:

sql = "select idip from ip where ip=%s and mask=%s" % (long(next_hop), 'DEFAULT')
cursor.execute(sql)
idnext_hop = cursor.fetchone()[0]

I get the following error:

    Inserting routes into table routes (1/377)...('insere_tabela_routes: Error on insertion at table routes, - SQL: ', 'select idip from ip where ip=0 and mask=DEFAULT')
1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Does anyone have a clue on what is the problem?

First of all, I hope the values you are passing aren't user-supplied because that's a SQL injection just waiting to happen. Second, have you tried printing the actual sql string value for debugging purposes? What does it say (verbatim)? Instead, don't use python string substitution, let it do that for you. — Arthur Dent
– Arthur Dent, Commented May 17, 2018 at 0:54
Yes, printing sql is inside the error message 'select idip from ip where ip=0 and mask=DEFAULT' I am pretty noob at sql so I'm still trying to understand how to do this selection with 2 values, once doing so with only one value works pretty well — Bruna Zamith
– Bruna Zamith, Commented May 17, 2018 at 1:03
Didn't work at all, now I get a different error message "Not all parameters were used in the SQL statement" — Bruna Zamith
– Bruna Zamith, Commented May 17, 2018 at 1:16
What MySQL library are you using? I suspect it's an odd one that does not use question marks for parameterized/sanitized queries. That would be important to know to help you with this. — Arthur Dent
– Arthur Dent, Commented May 17, 2018 at 1:24

Arthur Dent · Accepted Answer · 2018-05-17 01:39:45Z

1

mysql.connector uses %s instead of ? as the parameter marker, but you are circumventing it by using Python string formatting. Try this:

sql = "select idip from ip where ip=%s and mask=%s"
cursor.execute(sql, (long(next_hop), 'DEFAULT'))
idnext_hop = cursor.fetchone()[0]

answered May 17, 2018 at 1:39

Arthur Dent

1,96215 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bruna Zamith Over a year ago

That was exactly the solution. Thank you both, Arthur Dent and Gordon Linoff

Arthur Dent Over a year ago

@bruna if this helped you, please consider upvoting and marking as the solution / accepted answer. Otherwise future visitors of this question won't know which answer(s) helped and are correct.

Gordon Linoff · Accepted Answer · 2018-05-17 00:54:36Z

1

You are munging the query string with parameters, rather than passing them in as, well, parameters. The code should look like this:

sql = "select idip from ip where ip = ? and mask = ?"
cursor.execute(sql, (long(next_hop), 'DEFAULT'))
idnext_hop = cursor.fetchone()[0]

In other words, you want the query engine to do the substitution into the query. You don't want Python to do the substitution into the query string.

answered May 17, 2018 at 0:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

Bruna Zamith Over a year ago

When trying that, I get the error message: "Not all parameters were used in the SQL statement"

Gordon Linoff Over a year ago

@bruna . .. That's interesting. There are two parameters inthe query and two in the list.

Arthur Dent Over a year ago

@GordonLinoff it turns out OP is using mysql.connector, which does use %s as the parameter marker, but OP was trying to use python string formatting instead of letting cursor/query do the safe parameterized substitution. Unfortunately OP did not include this in original question - only learned it from a comment.. In case you want to edit.

Collectives™ on Stack Overflow

Python MySQL: Select with default value

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related