0

I try to select a random cell from the following sqllite table

  consonants   a    b   c
0          d  ad   bd  cd
1          e  ae  NaN  ce
2          f  af   bf  cf

First I generate a random column c from ['a','b','c'] and a random row r from ['d','e','f']. This works so far.

However, the following select statement always returns the value of c instead of the values of the column specified by c.

x = conn.execute('select ? from syllables WHERE consonants=?',(c,r)).fetchall()
# Returns [('b',)] if c='b' and r='f'

I verified, that the WHERE clause is correct by selecting all columns *.

x = conn.execute('select * from syllables WHERE consonants=?',(r)).fetchall()
# Returns [('f', 'af', 'bf', 'cf')] if r='f'

It also works if I manually retrieve a specific column like c='b'

x = conn.execute('select b from syllables WHERE consonants=?',(r)).fetchall()
# Returns [('bf',)] if r='f'

I do not really get why it always returns the value of c when passing it in qmark notation.

Kevin Streicher
  • 484
  • 1
  • 8
  • 25
  • You cannot specify variables (column or table names) using the `?`, only values (eg like in the where clause) – joris Feb 26 '16 at 15:34
  • See eg http://stackoverflow.com/questions/3247183/variable-table-name-in-sqlite – joris Feb 26 '16 at 15:36
  • This is interesting. I expected more or of an error by using it wrongly not by beeing it not supported. Is there any specific reason that variables cannot be specified by qmarks or was it just never implemented/supported? – Kevin Streicher Feb 26 '16 at 15:46
  • I believe it is to avoid issues with sql injection – Rolf of Saxony Feb 26 '16 at 15:54
  • Wasn't the whole point of the qmark notation to help against SQL injections. From what I read python doesn't have prepared statements but it has parameterized statements for %s, qmark and therelike. – Kevin Streicher Feb 26 '16 at 16:06
  • You don't get an error, because what you did is perfectly valid sql code (just not what you expected). You get something like `SELECT 'c' FROM table` (selecting a constant) which is valid and returns the constant value 'c'. – joris Feb 26 '16 at 17:08
  • If you add that as an answer I will select it as the correct one, as this is the reason for it. – Kevin Streicher Feb 27 '16 at 13:40

0 Answers0