Insert using a raw SQL query to avoid SQL injection?

Question

I have personalized tables and a bunch of composite keys in my database so I am using raw SQL queries to perform the CRUD operations needed. I found a way to make an insert following the documentation and it worked, but I am wondering, isn't this method vulnerable to SQL injection? I am using the user's input to insert this data and I don't see where's the sanitization of it, maybe I am just wrong though. Can you guide me? Here's my code:

    public function store(Request $request)
    {
    /*No composite keys here so I am using Eloquent*/
        $song = new Song();
        $song->code = $request->code;
        $song->title = $request->title;
        $song->artist = $request->artist;
        $song->length = $request->length;
        $song->album = $request->album;
        $song->save();

        $genre = new Genre();
        $genre->id_gen = $request->genre;
        $genre->id_song = $request->code;
        DB::insert('INSERT INTO genres (id_gen, id_song) values (?, ?)', [$genre->id_gen, $genre->id_song]);
        return $song;
    }

Jason · Accepted Answer · 2019-12-07 19:55:03Z

1

Using an insert in this fashion does not mean a SQL injection risk as this is what is known as a parameterized query. You are generating a query as a string, and then the database system performs its own properly managed replacements to construct the query. I can't recall if this is done at the database or driver level.

What you have done is exactly what Laravel does internally anyway (construct parameterized queries).

In terms of your actual code, you're doing something a little odd. By creating the Genre object, applying your request to it and saving, you're doing an insert anyway. There is no real need for the raw insert;

Be sure to validate your request object!

answered Dec 7, 2019 at 19:55

Jason

3,0402 gold badges24 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3532758 Over a year ago

To sum up, insert statement is safe, but remove that entire line because it's not necessary. Replace it with $genre->save() =)

Ravindra HV · Accepted Answer · 2019-12-07 19:51:35Z

0

The query uses placeholders (in other words the query-string can be stored as a template or as a constant).

This gives the sql-engine a chance to convert the query to a prepared-statement.

I do not have any knowledge of laravel but this looks like a legitimate use of prepared statements (and consequently immune to sql injection).

In other words the approach looks safe (atleast w.r.t sql-injection).

answered Dec 7, 2019 at 19:51

Ravindra HV

2,6161 gold badge22 silver badges27 bronze badges

Collectives™ on Stack Overflow

Insert using a raw SQL query to avoid SQL injection?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related