Clojurians Log v2

How can I reproduce a PostgreSQL hex->int cast (`x'beef'::int`) in HoneySQL?

@wombawomba I was going to suggest the PostgreSQL extensions to HoneySQL https://github.com/nilenso/honeysql-postgres but that doesn't seem to have anything so you're either going to need to use #raw (or honeysql.core/raw) or perhaps use a regular cast via #call etc?

yeah I’m already using that, but I couldn’t find anything for this

cast works, but I’m not sure how to create the hex literal (`cast(x'aa' AS int)` works but cast('aa' AS int) doesn’t)

If it's just a literal, use #raw

#raw "x'aa'::int"

ah, okay

any way to make that non-sql-injectable?

If it's a literal it can't be injected.

oh, sorry

and the reader tags only work on literals

I meant literal as in x'...' is the syntax for a “hex literal” in Postgres

I want the string itself (i.e. '...') to be parametrized

That doesn't make sense.

why not?

Okay, take a step back -- what actual problem are you trying to solve here? You asked about hex literals in each case.

x'..' has to be a literal. It's not a parameterizable thing.

oh okay, I think I see what you’re saying

So what I think you're asking is: "I have a hex string in Clojure but I want to pass it as an int to SQL..."?

my problem is that I have a bunch of queries like INSERT INTO foo (col, ...) VALUES (x'a123'::int8, ...) that I want to call from Clojure, with the ‘hex string’ parametrized

They are LITERALS.

well almost; I want to pass it as hex to postgres and then I want postgres to handle the conversion

but what you’re saying is that there’s no way to parametrize literals

Of course there isn't. They are literals.

If it is a literal value in your code, it cannot be an injection vector. Only values coming in from "outside" can be a vector for injection attacks.

right

If you have Clojure strings that are in hex, coming from "outside", those strings could be an injection vector -- but they are, by definition not literals, and you cannot use SQL's literal syntax on them.

yeah sorry, I didn’t realize there was no way to map a string to a (non-string) literal in SQL

If you have hex data from "outside", you need to convert that to numbers yourself.

I want the conversion to happen in the db though

but it sounds like this is more of a Postgres concern than a HoneySQL concern, so I guess I’ll take my question elsewhere

It's not specific to PostgreSQL. Parameters are entire values. ?::int8 would be legal, where you're passing in a number.

yeah, unfortunately however Postgres doesn’t do the hex conversion for that one

Of course it doesn't.

If you can find a PG function that accepts a hex string -- just the string portion, not with an x prefix -- and produces a number, that's going to be the way to go.

yeah, that’s what I was trying to say with “it’s a Postgres concern”

Based on my Bing results, this seems to be a fairly common question about PG and there really are no entirely good answers (several people on SO suggest bashing strings together to get a hex literal and passing that to PG but that's dangerous and open to attack vectors).

for the record, apparently the solution is basically to build a string and eval (EXECUTE) it (https://stackoverflow.com/a/8316731/2571391)

okay yeah, sounds like we came to the same conclusion 🙂

guess I’ll just have to convert the string in Clojure-land

This https://stackoverflow.com/questions/8316164/convert-hex-in-text-representation-to-decimal-number?noredirect=1 mentions a bn_in_hex() function but the comments suggest it is not available in all PG distributions.

(scroll past the various string-bashing "wrong" answers)

right yeah, seems like you’d need an extension for it

I really think the better approach is to do the hex to int conversion in Clojure and handle input validation there, and then pass the int to the SQL.

yeah, that’s what I’m thinking

it’ll make grepping my logs a bit trickier, but I guess I’ll have to cope

BTW, you wouldn’t happen to have any material on why parametrizing these ‘custom’ literals is impossible but parametrizing a regular string literal is fine? I got the impression you thought it was a dumb question but it doesn’t seem at all obvious to me why it’d be the case.

At what point in your code are you logging this? Couldn't you log before the hex->int conversion?

I'm not sure what to point you at re: parameterization -- this is just a matter of SQL syntax.

yeah, I do that too; but when there are (unexpected) SQL exceptions or when I’m doing debug logging I log the query data

Well, my thought was that since I can replace all literals I’ve encountered thus far (like 'foo', true, 123) with a parameter in a parametrized query, I should be able to do the same thing with this other type of literal x'fff'.

What I don’t see is what makes this distinction obvious; but I presume that’s because I’m not really familiar with the SQL standard or JDBC or wherever the distinction between these different types of literals is made.

Sorry, I just don't know how to explain it other than "syntax".

Alright :)

okay, after a bit of googling it seems like x'aa' is a literal for the “bit string” SQL type, and the problem here is actually that JDBC doesn’t have a way of sending bit strings (just for the record)