Drizzle Team•4d ago

Non-escape character breaks select function

One of my users has an apostrophe in their name ex: Ty'ler Marshall. They are just using out of the box Supabase email provider magic link functionality with a gmail address. The raw_user_meta_data column is storing their name like:

{
  "sub": "3ecc77d1-0dd178e7206d",
  "name": "Ty'ler Marshall",
  "email": "[email protected]",
  "email_verified": true,
  "phone_verified": false
}

{
  "sub": "3ecc77d1-0dd178e7206d",
  "name": "Ty'ler Marshall",
  "email": "[email protected]",
  "email_verified": true,
  "phone_verified": false
}

I then have a call on my front-end that is using Drizzle as ORM which is a pretty simple select:

    const results = await authDb(session, (tx) => {
      return tx.select().from(appUsers).where(eq(appUsers.user_id, user.id));
    });

    const results = await authDb(session, (tx) => {
      return tx.select().from(appUsers).where(eq(appUsers.user_id, user.id));
    });

But this is failing because its not being escaped. The value in DB is not escaped: Error getting user data Error: syntax error at or near "ler" The appUsers table doesn't even reference that col at all:

export const appUsers = pgTable('app_users', {
  id: bigint('id', { mode: 'number' })
    .generatedByDefaultAsIdentity()
    .primaryKey(),
  user_id: uuid('user_id')
    .references(() => authUsers.id, { onDelete: 'cascade' })
    .notNull(),
  phone: varchar('phone'),
  email: varchar('email'),
  phone_number_consented: boolean('phone_number_consented')
    .notNull()
    .default(false),
  email_consented: boolean('email_consented').notNull().default(false),
  created_at: timestamp('created_at').notNull().defaultNow(),
  updated_at: timestamp('updated_at').notNull().defaultNow(),
  user_initial_onboarding_complete: boolean('user_initial_onboarding_complete')
    .notNull()
    .default(false),
});

export const appUsers = pgTable('app_users', {
  id: bigint('id', { mode: 'number' })
    .generatedByDefaultAsIdentity()
    .primaryKey(),
  user_id: uuid('user_id')
    .references(() => authUsers.id, { onDelete: 'cascade' })
    .notNull(),
  phone: varchar('phone'),
  email: varchar('email'),
  phone_number_consented: boolean('phone_number_consented')
    .notNull()
    .default(false),
  email_consented: boolean('email_consented').notNull().default(false),
  created_at: timestamp('created_at').notNull().defaultNow(),
  updated_at: timestamp('updated_at').notNull().defaultNow(),
  user_initial_onboarding_complete: boolean('user_initial_onboarding_complete')
    .notNull()
    .default(false),
});

If I do this:

    const results = await db
      .select()
      .from(appUsers)
      .where(eq(appUsers.user_id, user.id));

    const results = await db
      .select()
      .from(appUsers)
      .where(eq(appUsers.user_id, user.id));

it works fine, does the tx.select() do some sort of full lookup in the auth users table that grabs all the info? I am not sure if this is a supabase, google, or drizzle situation. Any thoughts?

2 Replies

TylerOP•4d ago

After talking to Supabase peeps, pretty positive it's a drizzle issue (or at least not Supabase). I've identified it's not the selects, I think its the data inside the session object...

TylerOP•4d ago

I think this documentation here may be effected: https://orm.drizzle.team/docs/rls in that there are calls to things like:

await tx.execute(sql`
          -- auth.jwt()
          select set_config('request.jwt.claims', '${sql.raw(
            JSON.stringify(token)
          )}', TRUE);

await tx.execute(sql`
          -- auth.jwt()
          select set_config('request.jwt.claims', '${sql.raw(
            JSON.stringify(token)
          )}', TRUE);

But, if the token contains data with an apostrophe, it breaks that call.

Drizzle ORM - Row-Level Security (RLS)

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Gaming

Programming

Non-escape character breaks select function

Did you find this page helpful?