SQL Injection on Insert

SQL injection on INSERT

Injection can happen on any SQL statement not run properly.

For example, let's pretend your comment table has two fields, an integer ID and the comment string. So you'd INSERT as follows:

 INSERT INTO COMMENTS VALUES(122,'I like this website');

Consider someone entering the following comment:

'); DELETE FROM users; --

If you just put the comment string into the SQL without any processesing this could turn your single INSERT in to the following two statements followed by a comment:

INSERT INTO COMMENTS VALUES(123,''); DELETE FROM users; -- ');

This would delete everything from your users table. And there are people willing to spend all day finding the right tablename to empty using trial and error and various tricks. Here's a description of how you could perform an SQL Injection attack.

You need to use parameterized SQL statements to prevent this.

And this isn't just for security reasons. For example, if you're creating your SQL statements naively the following comment:

I'm just loving this website

would cause an SQL syntax error because of the apostrophe being interpreted by SQL as a closing quote.

How to make the SQL Injection on INSERT work on SQLite

Most SQL injections result in nothing useful to the perpetrator, just a syntax error.

For example, pass the string "I'm not satisfied" to this feedback function and the extra ' character would cause the quotes to be imbalanced, and this would result in an error, causing the INSERT to fail.

sqlite3.OperationalError: near "m": syntax error

That's technically SQL injection. The content interpolated into the query has affected the syntax of the SQL statement. That's all. It doesn't necessarily result in a successful "Mission: Impossible" kind of infiltration.

I can't think of a way to exploit the INSERT statement you show to make it do something clever, besides causing an error.

You can't change an INSERT into a SELECT that produces a result set. Even if you try to inject a semicolon followed by a second SQL query, you just get sqlite3.Warning: You can only execute one statement at a time

Your first try above resulted in a syntax error because you had both a VALUES clause and a SELECT as a source for the data to insert. You can use either one but not both in SQL syntax. See https://www.sqlite.org/lang_insert.html

You probably already know how to make the code safe, so unsafe content cannot even cause a syntax error. But I'll include it for other readers:

curs.execute("INSERT INTO feedback VALUES (?)", (feedback,))

PHP - Preventing SQL Injection on INSERT STATEMENT

Use PDO and prepared queries.Use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.Have a look at below example.

($conn is a PDO object)

$conn = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $conn->prepare("INSERT INTO users VALUES(:firstname, :lastname)");
$stmt->bindValue(':firstname', $firstname);
$stmt->bindValue(':lastname', $lastname);
$stmt->execute();

Improve SQL INSERT query to avoid sql injections

Make your query use parameters. Much less chance of injection:

cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))

credit (and more info) here: How to use variables in SQL statement in Python?

Also, Dan Bracuk is correct - make sure you validate your params before executing the SQL if you aren't already

Is DB::raw in insert method vulnerable to SQL Injection?

Yes, it's vulnerable to SQL Injection since the raw content of $uid will be injected to your sql query.

Although DB::raw() accepts prepared parameters, it cannot be used inside the insert method correctly.

To do that, you will need to write the insert query manually:

$uid = $request->input('uid');

DB::statement('INSERT INTO users (uid, created_at) VALUES (CONV(?, 16, 10), ?)', [
    $uid,
    date("Y-m-d H:i:s")
]);

Preventing SQL injection on insert

The most straightforward fix is to simply not build sql by concatenating strings together, and instead using params. If you're using SqlCommand you can do the following, otherwise do as @MarcB suggested

SqlCommand cmd = new SqlCommand("INSERT dbo.Table (field1, field2, field3) VALUES (@f1, @f2, @f3)", conn);

cmd.Paramters.Add("@f1", SqlDbType.VarChar, 50).Value = "abc";
cmd.Paramters.Add("@f2", SqlDbType.Int).Value = 2;
cmd.Paramters.Add("@f3", SqlDbType.VarChar, 50).Value = "some other value";

---

Related Topics