Fork me on GitHub
#off-topic
<
2022-03-21
>
Stuart11:03:30

Im in a situation where I need to string up SQL that has user input (user will specify a table name and a column name), I can't seem to parameterize this with C# SQL ADO. SO I want to validate before I run it (avoid sql injection), am I safe to just make sure the user input doesnt contain ; or go ? Only allow a-zA-Z\.\_- ?

Stuart11:03:29

The query will be off the form:

select top 1 {foo} from {bar} order by {foo} desc
Where user will supply foo as column name and bar as table name.

Stuart11:03:39

These will be read from a config file

p-himik11:03:27

You don't need to validate anything if you do proper escaping/quoting. Does the library that you're using have some API for escaping/quoting values?

Stuart11:03:00

It seems like it doesnt for table names and column names, e.g. this is safe

select Foo from Bar where Foo = @Foo
THen I can safely parameterize @Foo with sqlCommand.Parameters.AddWithValue("@Foo", foo); BUt again, doesnt work with table names or column names

seancorfield13:03:33

For now I'll just say read the docs. I'm on vacation.

seancorfield13:03:25

But the tl;Dr is : use honeysql

p-himik13:03:59

The built-in HoneySQL facilities only quote identifiers - there's no escaping:

(sql/format {:update :table :set {"foo-bar" 1 "baz/quux] = (DELETE FROM users), [x" 2}} {:dialect :sqlserver})
["UPDATE [table] SET [foo-bar] = ?, [baz/quux] = (DELETE FROM users), [x] = ?" 1 2]
That is, I couldn't find it - maybe I'm missing something.

seancorfield14:03:07

The ? Are sql parameters. They are fully escaped by definition

seancorfield14:03:11

Oh, you're talking about the keys in the hash map? Gotcha.

seancorfield14:03:12

There's some sanity checking on that but not much currently. Create a Github issue with that example and I'll give it some thought when I get back

seancorfield14:03:10

It's lunchtime here on vacation 😉

1
p-himik14:03:32

Although I don't think escaping identifiers is something that's supposed to be solved by a tool like HoneySQL - sure, I'll open an issue. Have fun! :) Edit: done - https://github.com/seancorfield/honeysql/issues/394

p-himik15:03:37

That would require a roundtrip to the DB or maybe coming up with very opaque statements. As I mentioned in the issue above, seems like escaping is rather simple if done along with quoting, so personally I'd go with that option.

isak16:03:21

It wouldn't, you would just construct + execute the SQL complete statement on the database server instead. For example:

declare @col sysname = 'id';
declare @table sysname = ';"] drop table users;';

declare @sql nvarchar(max) = (CONCAT('select top 1 ', QUOTENAME(@col), ' from ', QUOTENAME(@table)));
EXEC (@sql)
Where @col and @table could come from his C# code.

p-himik16:03:12

Well yeah, that's exactly what I meant by "opaque". Instead of simple SELECT you now have that monster.

isak16:03:14

Hmm seems pretty standard. But yea if he can easily validate it in C# I agree it is better. But then might need to replicate all of the ways to refer to tables in SQL Server, so might be more work.

Stuart16:03:47

Amazon delivery times are like windows file copy dialog. Package will be here today. Package will be here between 3 and 5 Package will be here between 3:15 and 5:15 Package will be here between 2 and 4 Package will be here before 10 PM

4
p-himik16:03:42

"We tried to deliver the package yesterday, but you weren't home."

Stuart16:03:19

> "We tried to deliver the package yesterday, but you weren't home." This is how Ted Kaczynski's are born.

😄 1
craftybones16:03:43

IF this were truly Windows, somewhere in your driveway is a stuck delivery agent who is asymptotically reaching your door

craftybones16:03:45

LOL @U013YN3T4DA. Your comment reminded me of another one recently. The commenter was talking about Yoko Ono’s singing on the Get Back documentary and he said “Yoko did for singers what John Wayne Gacy did for clowns”

Stuart17:03:24

It finally arrives, and its a wired mouse... I never even thought to check. I didn't think they even made wired mouse anymore.. fuck

p-himik17:03:08

I'm sorry for that, but it's also very funny. :D Of course they make such mice - plenty of reasons to prefer them.

Stuart17:03:05

THis is all to replace a mouse I lost somehow. I decided to go stay somewhere else for the weekend, but still needed to do some work so I took the work laptop and mouse. I distinctly remember picking up the mouse and switching it off, otherwise it might wake up laptop in the bag, which can be bad as it gets hot. THen I got to my destination and no mouse... I figured I must've picked it up, turned it off and stupidly put it back on desk... I get home on Sunday. No mouse here to be seen! It's just gone.

Stuart17:03:38

I can only assume I was burgled, but they left the computer, tv, watches, art etc and just took my mouse. Just to maximally annoy me.

lassemaatta18:03:04

Look at the bright side; I’d imagine it’s a lot harder to misplace a wired mouse :)

hiredman16:03:02

I wrote myself a little clojure and clojurescript app that scrapes delivery status information from usps, ups, and fedex and displays a list of packages with and estimated kph for how fast the package is going

😎 3
hiredman16:03:47

and I just got enough data that I've started using it to do forecasts, if a package is in city A using the data I have generate 100 possible deliveries, then see what the distribution of how many days it takes a package to get from A to me is

hiredman16:03:43

we moved from a big suburb to a small town a while back, and delivery service, even accuracy of estimate delivery dates is way worse

hiredman17:03:06

The great thing about writing my own forecasting is I get a histogram(likely inaccurate) instead of a single delivery date

👀 1
dvingo20:03:33

hope is happening in nyc this july! https://www.hope.net/

Nundrum20:03:51

@p-himik In case you're curious, I tried Xephyr and had odd mixed success. Xephyr would render to XScreenSaver's preview window, but not into the root window. Instead it would pop under the blank screen and be left there after dismissing the screensaver. 😄

p-himik20:03:03

Huh, alright. Thanks for letting me know. :)

adi22:03:47

x-posting since off-topic contains the superset of all Clojurians and I have a talk due in 2 days and I'm hoping someone gets nerdsniped into "helping" me a la "https://xkcd.com/386/"... 😁 https://clojurians.slack.com/archives/C053AK3F9/p1647901593337189?thread_ts=1647626343.230609&amp;cid=C053AK3F9