SQL Injection bug - D1 adaptor throws "Conversion failed: expected a datetime string in column" when string column contains any ISO date #25404
Description
Bug description
The D1 adaptor attempts to find a valid ISO date in any 'string' db column (see
) . If it doest, it attempts to convert it to a date format. The regex does not check to see if the ISO date is the only data in the column.If a string column contains any ISO date along with other string data, any create or find will throw the following error:
Invalid prisma.chat.create() invocation: Inconsistent column data: Conversion failed: expected a datetime string in column
Note that this is potentially a serious issue as ISO dates are not sanitized by SQL libraries. If any user generated string contains a valid ISO date, e.g. 2024-10-09T16:05:08.547Z anywhere, it will cause this error to occur.
How to reproduce
import { PrismaD1 } from '@prisma/adapter-d1'
export default {
async fetch(request, env, ctx) {
// Setup Prisma Client with the adapter
const adapter = new PrismaD1(env.MY_DB)
const prisma = new PrismaClient({ adapter })
const messages = "This is user input, 2024-10-09T16:05:08.547Z"
// Execute a Prisma Client query
const chat = await prisma.chat.create({
data: {
messages : JSON.stringify(messages),
},
})
// Return result
return new Response(usersCount)
},
}
Expected behavior
Data is written to database
But instead, fatal error is thrown - Invalid prisma.chat.create() invocation: Inconsistent column data: Conversion failed: expected a datetime string in column
Prisma information
// learn more about it in the docs: https://pris.ly/d/prisma-schema
generator client {
provider = "prisma-client-js"
previewFeatures = ["driverAdapters"]
}
datasource db {
provider = "sqlite"
url = env("DATABASE_URL")
}
model Chat {
id String @id @default(cuid())
createdAt DateTime? @default(now())
updatedAt DateTime? @updatedAt
messages String?
}
Environment & setup
- OS: macOS, cloudflare
- Database: Cloudflare D1
- Node.js version: v22.9.0
Prisma Version
prisma : 5.20.0
@prisma/client : 5.20.0
Computed binaryTarget : darwin-arm64
Operating System : darwin
Architecture : arm64
Node.js : v22.9.0
Query Engine (Node-API) : libquery-engine 06fc58a368dc7be9fbbbe894adf8d445d208c284 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Schema Engine : schema-engine-cli 06fc58a368dc7be9fbbbe894adf8d445d208c284 (at node_modules/@prisma/engines/schema-engine-darwin-arm64)
Schema Wasm : @prisma/prisma-schema-wasm 5.20.0-12.06fc58a368dc7be9fbbbe894adf8d445d208c284
Default Engines Hash : 06fc58a368dc7be9fbbbe894adf8d445d208c284
Studio : 0.502.0
Preview Features : driverAdapters