Thread: quoting values magic
Assume I have an UPDATE statement that looks like
UPDATE foo
SET
pattern = '$pattern',
shape = '$shape',
length = $length,
comment = '$comment'
WHERE foo_id = $foo_id
and length is defined as NUMERIC. Is there any kind of magic that
would allow me to use the SQL above as is even if $length is not
defined? In other words, I'd like to avoid having to modify the SQL
to include or not include "length = $length" based on whether or not
$length is defined as it's acceptable for it to be NULL in foo.
I can't say "length = '$length'" as '' is not valid input for NUMERIC.
Hope that makes sense?
Thanks.
--
Brandon
Brandon Metcalf wrote: > Assume I have an UPDATE statement that looks like > > UPDATE foo > SET > pattern = '$pattern', > shape = '$shape', > length = $length, > comment = '$comment' > WHERE foo_id = $foo_id > > and length is defined as NUMERIC. Is there any kind of magic that > would allow me to use the SQL above as is even if $length is not > defined? In other words, I'd like to avoid having to modify the SQL > to include or not include "length = $length" based on whether or not > $length is defined as it's acceptable for it to be NULL in foo. No. There's a reason SQL is a pain in the ass to work with at an application level and people are always coming up with abstraction layers and generators for it. What language are you using? Are you familiar with the problem of SQL injection? You need to be really paranoid about user input. Otherwise someone will do this to you: http://xkcd.com/327/ ... so you've got more to worry about than just handling undefined inputs. Consider using existing tools provided in your database driver or language that handle these issues for you. > I can't say "length = '$length'" as '' is not valid input for NUMERIC. ... and because if it was, it'd probably map to NULL, so you wouldn't be saying "don't change `length'" you'd be saying "set length to NULL". Do you want to leave length unchanged if $length is undefined? or do you want to set length to null if $length is undefined? If you want to set length to null if $length is undefined, use a parameterised query. Any decent database driver will map "undefined" variables in a language to NULL parameters, so you can do something like: $sql = " UPDATE foo SET pattern = ?, shape = ?, length = ?, comment = ? WHERE foo_id = ? "; db.execute($sql, $pattern, $shape, $length, $comment, $foo_id); ( adapt as necessary to your language's syntax, database driver, parameter placeholder syntax, etc). If you wanted to leave `length' unchanged when $length is not defined, instead of setting it to null, you could use the following somewhat ugly trick: $sql = " UPDATE foo SET pattern = ?, shape = ?, length = COALESCE(?, foo.length), comment = ? WHERE foo_id = ? "; db.execute($sql, $pattern, $shape, $length, $comment, $foo_id); -- Craig Ringer
On 2009-05-22, Brandon Metcalf <brandon@geronimoalloys.com> wrote:
> Assume I have an UPDATE statement that looks like
>
> UPDATE foo
> SET
> pattern = '$pattern',
> shape = '$shape',
> length = $length,
> comment = '$comment'
> WHERE foo_id = $foo_id
>
> and length is defined as NUMERIC. Is there any kind of magic that
> would allow me to use the SQL above as is even if $length is not
> defined?
no, but you can get the same effect in a different way.
> In other words, I'd like to avoid having to modify the SQL
> to include or not include "length = $length" based on whether or not
> $length is defined as it's acceptable for it to be NULL in foo.
> I can't say "length = '$length'" as '' is not valid input for NUMERIC.
>
> Hope that makes sense?
option 1: $length = "length"
If your language of choice (it appears to be similar to shell, PHP, or Perl)
allows you to store the string value "length" in your $length variable
then the existing values of length will be retained in the update.
option 2: case when '$length' = '' ...
you can use case like this:
UPDATE foo
SET
pattern = '$pattern',
shape = '$shape',
length = case when '$length'='' then length else '$length' end,
comment = '$comment'
WHERE foo_id = $foo_id
here you can substitute any value you choose for the empty string,
0 or NULL may (or may not) be more apropriate.
j == jasen@xnet.co.nz writes: j> On 2009-05-22, Brandon Metcalf <brandon@geronimoalloys.com> wrote: j> > Assume I have an UPDATE statement that looks like j> > j> > UPDATE foo j> > SET j> > pattern = '$pattern', j> > shape = '$shape', j> > length = $length, j> > comment = '$comment' j> > WHERE foo_id = $foo_id j> > j> > and length is defined as NUMERIC. Is there any kind of magic that j> > would allow me to use the SQL above as is even if $length is not j> > defined? j> no, but you can get the same effect in a different way. j> > In other words, I'd like to avoid having to modify the SQL j> > to include or not include "length = $length" based on whether or not j> > $length is defined as it's acceptable for it to be NULL in foo. j> > I can't say "length = '$length'" as '' is not valid input for NUMERIC. j> > j> > Hope that makes sense? j> option 1: $length = "length" j> If your language of choice (it appears to be similar to shell, PHP, or Perl) j> allows you to store the string value "length" in your $length variable j> then the existing values of length will be retained in the update. j> option 2: case when '$length' = '' ... j> you can use case like this: j> UPDATE foo j> SET j> pattern = '$pattern', j> shape = '$shape', j> length = case when '$length'='' then length else '$length' end, j> comment = '$comment' j> WHERE foo_id = $foo_id j> here you can substitute any value you choose for the empty string, j> 0 or NULL may (or may not) be more apropriate. The issue here is that these reduce back to my original problem. For example, if I use a CASE statement and I fall through to the ELSE, then the SQL is attempting to insert a "''" in a NUMERIC field which is not valid. That is, it's trying to do UPDATE foo SET length='' WHERE foo_id=$foo_id This fails. It's exactly this problem I'm trying to avoid by some slick use of SQL. I can always generate the SQL on the fly based on the value of $length, but I'd like to avoid this if possible. Maybe I'm misunderstanding your suggestion. -- Brandon
On May 26, 2009, at 6:37 PM, Brandon Metcalf wrote:
> j> option 2: case when '$length' = '' ...
>
> j> you can use case like this:
>
> j> UPDATE foo
> j> SET
> j> pattern = '$pattern',
> j> shape = '$shape',
> j> length = case when '$length'='' then length else
> '$length' end,
> j> comment = '$comment'
> j> WHERE foo_id = $foo_id
>
> j> here you can substitute any value you choose for the empty string,
> j> 0 or NULL may (or may not) be more apropriate.
>
>
> The issue here is that these reduce back to my original problem. For
> example, if I use a CASE statement and I fall through to the ELSE,
> then the SQL is attempting to insert a "''" in a NUMERIC field which
> is not valid. That is, it's trying to do
No it doesn't, read that statement again ;)
If $length = 'foo' it reads (leaving out the extra fields):
UPDATE foo
SET length = CASE WHEN 'foo'='' THEN length ELSE 'foo' END
WHERE foo_id = $foo_id;
Which evaluates to:
UPDATE foo SET length = 'foo' WHERE foo_id = $foo_id;
Whereas if $length = '' it reads:
UPDATE foo
SET length = CASE WHEN ''='' THEN length ELSE '' END
WHERE foo_id = $foo_id
Which evaluates to:
UPDATE foo SET length = length WHERE foo_id = $foo_id
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4a1c2f7010091048315763!
d == dalroi@solfertje.student.utwente.nl writes: d> On May 26, 2009, at 6:37 PM, Brandon Metcalf wrote: d> > j> option 2: case when '$length' = '' ... d> > d> > j> you can use case like this: d> > d> > j> UPDATE foo d> > j> SET d> > j> pattern = '$pattern', d> > j> shape = '$shape', d> > j> length = case when '$length'='' then length else d> > '$length' end, d> > j> comment = '$comment' d> > j> WHERE foo_id = $foo_id d> > d> > j> here you can substitute any value you choose for the empty string, d> > j> 0 or NULL may (or may not) be more apropriate. d> > d> > d> > The issue here is that these reduce back to my original problem. For d> > example, if I use a CASE statement and I fall through to the ELSE, d> > then the SQL is attempting to insert a "''" in a NUMERIC field which d> > is not valid. That is, it's trying to do d> No it doesn't, read that statement again ;) Oops. Indeed, you are correct. -- Brandon
Brandon Metcalf <brandon@geronimoalloys.com> writes:
> d == dalroi@solfertje.student.utwente.nl writes:
> d> On May 26, 2009, at 6:37 PM, Brandon Metcalf wrote:
> d> > The issue here is that these reduce back to my original problem. For
> d> > example, if I use a CASE statement and I fall through to the ELSE,
> d> > then the SQL is attempting to insert a "''" in a NUMERIC field which
> d> > is not valid. That is, it's trying to do
> d> No it doesn't, read that statement again ;)
> Oops. Indeed, you are correct.
I think there is a problem though. If you have
case when '$length'='' then length else '$length' end
then what the parser is going to see is a CASE expression with a
variable (known to be NUMERIC) in one arm and an unknown-type literal
constant in the other arm. So it's going to decide that the literal
must be NUMERIC too, and that type coercion will fail if the literal
is really just ''.
Some experimentation suggests that you might get away with
case when '$length'='' then length else '$length'::text::numeric end
so that the text-to-numeric conversion is delayed to runtime. However
this is a bit fragile (it's dependent on some undocumented details of
the constant-expression-folding behavior) and it also requires
hardwiring knowledge that length is indeed numeric into your SQL
command.
On the whole I'd suggest going with NULL, not empty string, as your
representation of a missing update value if at all possible. Then
the previously-suggested COALESCE solution will work, and you aren't
relying on any shaky assumptions about when and how the parser will
try to enforce validity of the datatype value.
regards, tom lane
t == tgl@sss.pgh.pa.us writes: t> Brandon Metcalf <brandon@geronimoalloys.com> writes: t> > d == dalroi@solfertje.student.utwente.nl writes: t> > d> On May 26, 2009, at 6:37 PM, Brandon Metcalf wrote: t> > d> > The issue here is that these reduce back to my original problem. For t> > d> > example, if I use a CASE statement and I fall through to the ELSE, t> > d> > then the SQL is attempting to insert a "''" in a NUMERIC field which t> > d> > is not valid. That is, it's trying to do t> > d> No it doesn't, read that statement again ;) t> > Oops. Indeed, you are correct. t> I think there is a problem though. If you have t> case when '$length'='' then length else '$length' end t> then what the parser is going to see is a CASE expression with a t> variable (known to be NUMERIC) in one arm and an unknown-type literal t> constant in the other arm. So it's going to decide that the literal t> must be NUMERIC too, and that type coercion will fail if the literal t> is really just ''. t> Some experimentation suggests that you might get away with t> case when '$length'='' then length else '$length'::text::numeric end I think this is what I meant to say :) If $length contains a number, then the resulting statement will be $length = '5.8', for example, and this will fail for type NUMERIC. -- Brandon