Home > Archive > Tcl > June 2007 > DB problem with strings....
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
DB problem with strings....
|
|
|
| My code:
dbh eval "delete from departments"
set str "Al's automotive"
dbh eval {insert into departments values (1000,$str)}
set str "Al's automotive EEEEE"
dbh eval {update departments set name='$str' where id=1000}
> Insert seems to work fine
> update produces "$str"
if I do:
dbh eval "update departments set name='$str' where id=1000"
I get an error about ('s)
Please help
| |
| Gerald W. Lester 2007-06-24, 8:11 am |
| Mel wrote:
> My code:
>
> dbh eval "delete from departments"
>
> set str "Al's automotive"
> dbh eval {insert into departments values (1000,$str)}
>
> set str "Al's automotive EEEEE"
> dbh eval {update departments set name='$str' where id=1000}
>
>
>
> if I do:
> dbh eval "update departments set name='$str' where id=1000"
>
> I get an error about ('s)
What type of DB (SQLite, Oracle, ...)?
--
+--------------------------------+---------------------------------------+
| Gerald W. Lester |
|"The man who fights for his ideals is the man who is alive." - Cervantes|
+------------------------------------------------------------------------+
| |
|
| On Jun 23, 2:50 pm, "Gerald W. Lester" <Gerald.Les...@cox.net> wrote:
> Mel wrote:
>
>
>
>
>
>
>
> What type of DB (SQLite, Oracle, ...)?
>
> --
> +--------------------------------+---------------------------------------+
> | Gerald W. Lester |
> |"The man who fights for his ideals is the man who is alive." - Cervantes|
> +------------------------------------------------------------------------+- Hide quoted text -
>
> - Show quoted text -
sqLite
| |
| Christian Nassau 2007-06-24, 8:11 am |
| Mel wrote:
> My code:
>
> dbh eval "delete from departments"
>
> set str "Al's automotive"
> dbh eval {insert into departments values (1000,$str)}
>
> set str "Al's automotive EEEEE"
> dbh eval {update departments set name='$str' where id=1000}
>
>
>
> if I do:
> dbh eval "update departments set name='$str' where id=1000"
Here Tcl substitutes $str before invoking the dbh command, so your query
becomes "update departments set name='Al's automotive' where id=1000"
which is invalid SQL.
The solution is to always brace the query:
dbh eval {update departments set name=$str where id=1000}
If you feel that you must use quotes instead of braces it becomes your
own responisbility to always quote all values properly. You could use
dbh onecolumn {select quote($somevar)}
to do this.
HTH,
--
=> Christian Nassau, http://www.nullhomotopie.de
| |
| TronyQ 2007-06-24, 8:11 am |
| On Jun 23, 2:02 pm, Mel <MelHer...@gmail.com> wrote:
> My code:
>
> dbh eval "delete from departments"
>
> set str "Al's automotive"
> dbh eval {insert into departments values (1000,$str)}
>
> set str "Al's automotive EEEEE"
> dbh eval {update departments set name='$str' where id=1000}
>
>
> if I do:
> dbh eval "update departments set name='$str' where id=1000"
>
> I get an error about ('s)
>
> Please help
Haven't tried this but I suspect the db handler is seeing the ' in the
"Al's" string and thinking your entry is "Al" with junk after it. Did
you try escaping the quote in the Al's string? Perhaps run a quick
legalization on the string before trying to update?
regsub -all {(['])} str {\\\1} str
| |
| TronyQ 2007-06-24, 8:11 am |
| On Jun 23, 11:13 pm, TronyQ <jason.t.gen...@gmail.com> wrote:
> On Jun 23, 2:02 pm, Mel <MelHer...@gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
> Haven't tried this but I suspect the db handler is seeing the ' in the
> "Al's" string and thinking your entry is "Al" with junk after it. Did
> you try escaping the quote in the Al's string? Perhaps run a quick
> legalization on the string before trying to update?
>
> regsub -all {(['])} str {\\\1} str
Oops, I meant:
regsub -all {(['])} $str {\\\1} str
| |
| Donal K. Fellows 2007-06-24, 8:11 am |
| Mel wrote:
> dbh eval "update departments set name='$str' where id=1000"
SQLite knows about Tcl variables, and so the right approach is this:
dbh eval {update departments set name=$str where id=1000}
The two changes here are:
1) Use {} instead of "" so that SQLite sees what you write exactly
2) Use $str instead of '$str' so that you're using the contents of a
variable and not a funny string literal
Remember, SQLite knows about Tcl variables (unlike some DB interfaces;
I'm looking at *you*, mysqltcl...) and this means that you don't need to
go down the well-paved route to hell via quoting incantations and SQL
injection vulnerabilities.
Donal.
| |
| Goeran Hanke 2007-06-25, 4:23 am |
| Hello,
Mel wrote:
> set str "Al's automotive"
> ...
> dbh eval "update departments set name=3D'$str' where id=3D1000"
>=20
> I get an error about ('s)
In SQL you have to quote the ', this character is the string delimiter:
proc SQLstr {str} { return '[string map {' ''} $str]' }
set str "Al's automotive"
dbh eval "update departments set name=3D[SQLstr $str] where id=3D1000"
Greetings G=F6ran
--=20
string map { DOT . AT @ } goeranDOThankeATgmxDOTde
| |
| Donal K. Fellows 2007-06-25, 4:23 am |
| Donal K. Fellows wrote:
> SQLite knows about Tcl variables, and so the right approach is this:
>
> dbh eval {update departments set name=$str where id=1000}
>
> The two changes here are:
> 1) Use {} instead of "" so that SQLite sees what you write exactly
> 2) Use $str instead of '$str' so that you're using the contents of a
> variable and not a funny string literal
Another way of thinking about this that might help is that when you
are working with SQL, you are working with a programming language, and
not a simple string. When you write sub-programs in Tcl, you use
braces, like this:
foreach abc $someList {
puts "got some kind of $abc"
}
Well, in SQL it's the same:
dbh eval {
-- I'm bored of using a single line...
update departments
set name = $str
where id = 1000
}
Once you think of it that way, you realize that SQL and Tcl are a
match made in heaven, and that SQLite *gets it right*.
Donal.
|
|
|
|
|