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:
std::string name_str = "Bob"; \\! User input unsafe!!
std::string email_str = "bob@gmail.com"; \\! 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)
std::string name_str = "Bob"; \\! User input unsafe!!
std::string email_str = "bob@gmail.com"; \\! 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?
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.