PostgresSQL - SQL Prepared Statement vs String Escaping preventing SQL injection attacks

0
votes

Hi I'm writing an c++ application using libpqxx to insert rows into a Postgres SQL Table and the data being written is user inputted so I need to guard against SQL injection attacks. From what I see online I can take two approaches:

  1. Prepared Statements
std::string name_str = "Bob";            \\! User input unsafe!!
std::string email_str = "[email protected]"; \\! User input unsafe!!

pqxx::connection con(c_string);

std::string insert_str = "INSERT INTO users(name, email) VALUES ($1, $2)";
con.prepare("insert_to_users", insert_str);

pqxx::work insert_work(con);

insert_work.exec_prepared("insert_to_users", name_str, email_str)
  1. String Escaping
std::string name_str = "Bob";            \\! User input unsafe!!
std::string email_str = "[email protected]"; \\! User input unsafe!!

pqxx::connection con(c_string);
pqxx::work insert_work(con);

std::string insert_str = "INSERT INTO users(name, email)"
                          "VALUES ('" + insert_work.esc(name_str) + "' , '" + insert_work.esc(email_str) + "')";

insert_work.exec(insert_str)

My application isn't going to keep the database connection alive therefore the prepared statement is only ever going to be used once and then destroyed, so is it over-kill to use a prepared statement?

Does string escaping provide protection from all SQL injection vulnerabilities? Or is there a better way of doing it?

1
c++postgresqllibpqlibpqxx
You title already contains SQL injection attacks. The answer is here. String escaping prevents unwanted characters to end in the database, but offers no protection against SQL injection which is about having the SQL engine wrongly interprets the end of the query as a different query. Just goolgling for it immediately gave nice examples here – Serge Ballesta
@SergeBallesta The only way it could be interpreted as a different query is if it were not properly escaped to start with. – jjanes

1 Answers

1
votes

My application isn't going to keep the database connection alive therefore the prepared statement is only ever going to be used once

If you are worried about performance, you should fix this single-use connection issue. And if you are not worried about performance, then why do you care if prepared statements are "over-kill"?

While either should work, the first one is cleaner, and less likely for someone to screw up in the future.