@prologic@twtxt.net There have always been and there will always be people who have absolutely no clue what they’re doing. I’ve been 100% one of them when I started. Guaranteed, heaps of new SQL injections are born every single day, numbers rising.

That doesn’t justify all the WAF crap in the first place, though. In my opinion it’s just a filthy plaster applied to an injected wound. The software itself must be secure. Otherwise, don’t put that shit on the internet. Probably not even operate it at all. Nowhere. Fix it or throw it in the bin.

One thing about my design here is that it would no longer incorporate “regex”-based rules like OWASP, mostly because my experience thus far has taught me that these rules are kind of overly sensitive, produce false positives and I’m not sure they are really very effective. For example, why is the point of performing SQL injection detection at the Edge using a WAF if you already handle SQL properly in the first place? (seriously does anyone still construct SQL queries by hand with effectively printf?!)

Today is the day where everything is falling apart. Suddenly, I get: SQL logic error: cannot start a transaction within a transaction

I was trying to optimize the SQL query used for the Compact FrontPage (anonymous view for Discovery when the Admin/Operator chooses “one twt per feed”).

SQL scares me i tweaked a bash script that pulled from a DB and the bash part was easy even if i was just going off of the code in there that i didn’t write (like i understood it at least) but the SQL parts had me suffering

The other day, after a discussion online, we came to the conclusion that using awk+sed+tr could replace much of the development that requires a database. However, using SQLite to have a SQL syntax isn’t a bad idea either. What do you think?

@lyse@lyse.isobeef.org OK. So how I have worked things like this out is to have the interface in the root package from the implementations. The interface doesn’t need to be tested since it’s just a contract. The implementations don’t need to import storage.Storage

• storage/ defines the Storage interface (no tests!)
  
  • storage/sqlite for the sqlite implementation tests for sqlite directly
    
  • storage/ram for the ram implementation and tests for RAM directly
    
• controller/ can now import both storage and the implementation as needed.
  

So now I am guessing you wanted the RAM test for testing queries against sqlite and have it return some query response?

For that I usually would register a driver for SQL that emulates sqlite. Then it’s just a matter of passing the connection string to open the registered driver on setup.

https://github.com/glebarez/go-sqlite?tab=readme-ov-file#connection-string-examples

Learn SQL by solving crimes. I want to highlight it as a teacher and a developer, it’s extremely well done.
https://www.sqlnoir.com/
#sql

I share I did write up an algorithm for it at some point I think it is lost in a git comment someplace. I’ll put together a pseudo/go code this week.

Super simple:

Making a reply:

1. If yarn has one use that. (Maybe do collision check?)
   
2. Make hash of twt raw no truncation.
   
3. Check local cache for shortest without collision
   
   • in SQL: select len(subject) where head_full_hash like subject || '%'
     

Threading:

1. Get full hash of head twt
   
2. Search for twts
   
   • in SQL: head_full_hash like subject || '%' and created_on > head_timestamp
     

The assumption being replies will be for the most recent head. If replying to an older one it will use a longer hash.

@prologic@twtxt.net, are you running Gitea with an SQL backend, or using sqlite? Any reason have haven’t moved to Forgejo?

it uses the queries you define for add/del/set/keys. which corrispond to something like INSERT INTO <table> (key, value) VALUES ($key, $value), DELETE ..., or UPDATE ...

the commands are issued by using the maddycli but not the running maddy daemon.

see https://maddy.email/reference/table/sql_query/

the best way to locate in source is anything that implements the MutableTable interface… https://github.com/foxcpp/maddy/blob/master/framework/module/table.go#L38

SQL(ite) and zettelkastens as a medium for non-linear story telling. #halfbaked

user-defined order in SQL [[https://begriffs.com/posts/2018-03-20-user-defined-order.html]] #sql #links maybe something that can be adapted to !sqlite?

Testing @!(ref “weewiki_sql” “page markers” “iterate_over_values”)!@.

While certainly not a solution to everything, I find I’m using temporary SQLITE database a bunch to solve problems with a few lines of sql and less then 50 lines of code (to insert data into the SQLITE DB) instead of several hundred of lines of code and a bunch of arrays.