1

Using tclodbc with the revnum variable containing 77777, the following statement gets the error “ERROR: invalid input syntax for integer: "$revnum"”

$db {insert into (revnum,risetime,settime,sat,passlen,riseges,setges,elevation)
    values('$revnum','1111-11-1111:11:11:11','1111-11-1111:11:11','C4','24','QQ','QQ','22');}

But this statement works:

$db {insert into (revnum,risetime,settime,sat,passlen,riseges,setges,elevation)
    values('77777','1111-11-11 :11','1111-11-1111:11:11','C4','24','QQ','QQ','22');}

Why wont the the value of the variable revnum insert into the database?

Donal Fellows
  • 133,037
  • 18
  • 149
  • 215
user1215496
  • 21
  • 1
  • 2
  • 3
    `$revnum` isn't being interpolated so the database gets `$revnum` where it expects an integer. I don't know enough TCL to show you how to do it properly though. [This answer](http://stackoverflow.com/a/2194381/479863) might help though. – mu is too short Feb 17 '12 at 06:22
  • Better retag as "tclodbc" rather than "postgresql" as the question per se has nothing to do with the indicated DBMS implementation. – kostix Feb 17 '12 at 07:30

1 Answers1

3

mu is too short gave a correct answer: the solution is to learn how grouping of characters works in Tcl before trying to insert something to a database.

But note well that your code is inherently flawed because you do not use paraterized queries, which is what every sensible programmer is supposed to use when querying the database programmatically. Read about the statement subcommand of a database object in the tclodbc manual. Basically you should do:

$db statement ins {insert into (revnum, risetime, settime, sat,
                                passlen, riseges, setges, elevation)
                   values (?, ?, ?, ?, ?, ?, ?, ?)}
$ins $revnum 1111-11-1111:11:11:11 C4 24 QQ QQ 22

That way, the ODBC backend driver takes care of properly escaping all the values preventing SQL injection no matter where the values come from and what they contain.

kostix
  • 51,517
  • 14
  • 93
  • 176
  • 1
    If it was possible to use `tdbc::odbc`, that would make this even easier. – Donal Fellows Feb 17 '12 at 09:54
  • 1
    I should add that [TDBC](http://tdbc.tcl.tk) has a native PostgreSQL driver so if using TDBC is an option, one could use `tdbc::postgres` right away. – kostix Feb 17 '12 at 11:04
  • As a slight comment, parameterized queries are where a sensible programmer should go *first*, but there are cases where they're not a realistic option. For example, when inserting many rows it can be inconvenient at best to do so. – RHSeeger Feb 17 '12 at 13:50
  • @RHSeeger When inserting many rows is exactly where you want a parameterized query (with appropriate wrapping of a transaction, checkpoints, etc.) The main contra-indication for parameterization is when you've got to have some generated non-value parts of the SQL (e.g., table names or field names). Those _can't_ be parameterized (well, not without a stored procedure to act as a thunk/shim). – Donal Fellows Feb 18 '12 at 08:51
  • The problem I've run into with parameterized queries is when you want to do something of the form [insert into mytable (x,y,z) values (1,2,3),(3,4,5),,,,,(100,101,102)]. Specifically, when you have lots of value elements. When you're building the list from lists of list with joins, it seems hard to use them. – RHSeeger Feb 18 '12 at 14:13