Archive for the ‘Database Development’ Category
Posted by jpluimers on 2025/04/24
[Wayback/Archive] One-liner for running queries against CSV files with SQLite | Simon Willison’s TILs
I figured out how to run a SQL query directly against a CSV file using the sqlite3 command-line utility:
sqlite3 :memory: -cmd '.mode csv' -cmd '.import taxi.csv taxi' \
'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'
This uses the special :memory: filename to open an in-memory database. Then it uses two -cmd options to turn on CSV mode and import the taxi.csv file into a table called taxi. Then it runs the SQL query.
sqlite3 :memory: -cmd '.import -csv taxi.csv taxi' \
'SELECT passenger_count, COUNT(*), AVG(total_amount) FROM taxi GROUP BY passenger_count'
Via [Wayback/Archive] Simon Willison on Twitter: “TIL you can run SQL queries directly against CSV files as a one-liner using the default sqlite3 command line utility”
Read the rest of this entry »
Posted in CSV, Database Development, Development, Software Development, SQLite | Leave a Comment »
Posted by jpluimers on 2025/04/22
From a long time ago, but the (impressive!) leaderboard still has the same order (the Rust implementation did get faster!).
[Wayback/Archive] Towards Inserting One Billion Rows in SQLite Under A Minute – blag
Recently, I ran into a situation where I needed a test database with lots of rows and needed it fast. So I did what any programmer would do: wrote a Python script to generate the DB. Unfortunately, it was slow. Really slow. So I did what any programmer would do: went down the rabbit hole of learning more about SQLite, Python, and eventually Rust… in my quest to get a 1B row database under a minute. This blog post is a summary of this fun and educational exercise.
Repository at [Wayback/Archive] avinassh/fast-sqlite3-inserts: Some bunch of test scripts to generate a SQLite DB with 1B rows in fastest possible way
Leaderboard
(for 100M insertions)
| Variant |
Time |
| Rust |
23 seconds |
| PyPy |
126 seconds |
| CPython |
210 seconds |
Via: [Wayback/Archive] Shawn Wildermuth #000000 { lives: matter; } 🇺🇦 on Twitter: “Towards Inserting One Billion Rows in SQLite Under A Minute””
--jeroen
Read the rest of this entry »
Posted in Database Development, Development, SQLite | Tagged: 000000 | Leave a Comment »
Posted by jpluimers on 2025/03/06
2.5 years after Miguel summarised the state of AI text models, and given SQL Injection (because of mixing control and data channels) still is a thing in the 2020’s, I wonder both how much improvement there has been on the AI side of things and how much it is used in pen testing.
So I archived the below tweets to be able to read back and figure out on the current state.
[Wayback/Archive] Miguel de Icaza on Twitter: “This is so beautiful – SQL Injection attacks but for GPT-3 and other AI text models.”:
Read the rest of this entry »
Posted in AI and ML; Artificial Intelligence & Machine Learning, Blue team, Database Development, Development, Pen Testing, Power User, Red team, Security, Software Development, SQL | Leave a Comment »
Posted by jpluimers on 2025/02/11
For my link archive: [Wayback/Archive] Payload Box.
It has lots of examples on payloads for various kinds of injections that are excellent teaching material.
Covered are Cross Site Scripting (XSS), SQL Injection, Server Side Template Injection, RFI/LFI, Command Injection, CSV Injection, Directory, Open Redirect and XML External Entity (XXE) Injection.
Got there when inspired by:
Read the rest of this entry »
Posted in Blue team, Database Development, Development, Power User, Red team, Security, Software Development, SQL, Web Development | Leave a Comment »
Posted by jpluimers on 2025/01/23
I love how Kristian Köhntopp often turns series of valuable tweets in a blog post. [Wayback/Archive] MySQL: Boiling JFrogs | Die wunderbare Welt von Isotopp is no different and has much more than the few quotes below (especially about the process of finding the solutions):
Read the rest of this entry »
Posted in Database Development, Development, MySQL, Profiling, Profiling-Performance-Measurement, Software Development | Leave a Comment »
Posted by jpluimers on 2025/01/07
For my link archive: [Wayback/Archive] MySQL: Row Literals | Die wunderbare Welt von Isotopp
Question on the Libera/#mysql IRC channel:
Is there a way to split a simple select into multiple returned rows? For example, select 1, 2, 3 to be returned as rows?
This is actually asking for a table literal notation. I know of four ways to construct a table literal in MySQL:
They are based on UNION ALL, JSON_TABLE and VALUES statement (the latter in two forms). I knew about the first (which I used in other database environments), not about the others.
Read the rest of this entry »
Posted in Database Development, Development, MySQL, SQL | Leave a Comment »
The Wiert Corner - irregular stream of stuff 