Prepared statements vs. escaping strings in PHP SQLite3

Question

I'm working with SQLite3 in PHP.

A friend told me that I should prefer prepared statements, and I read this in many posts here on StackOverflow too. I want to understand the benefits of using prepared statements because I have to write much more lines, than regular escaping like using:

$db->exec("Insert INTO table VALUES(NULL,'".$db->escapeString($value)."')"

or using:

foreach($_POST as $k => $v) {
    $_POST[$k] = $db->escapeString($v);
}

Why should I use prepared statements? What are the pros and cons?

Possible duplicates: http://stackoverflow.com/q/14535337/472495, http://stackoverflow.com/q/8263371/472495, http://stackoverflow.com/q/5200238/472495 — halfer, Mar 19 '13 at 11:32

Your Common Sense · Accepted Answer · 2013-03-19T11:54:16.697

2

People often confuse parameters with native prepared statements. I doubt Sqlite supports the latter, but nevertheless you have to ALWAYS use parameters to substitute the actual data in the query.

When parameter (also called "placeholder") being processed, important things happen:

ALL the required formatting (i.e. escaping and quoting) applied (while escaping will do only half of the job - leaving your query wide open to injection).
such a formatting being applied to the very data that is going to the query, not to the source variable, spoiling it (so, you can send it via email or store in a session).
such a formatting being applied automatically, making your safety independent from the good will of developers. that's highly important matter

edited Mar 19 '13 at 11:54

answered Mar 19 '13 at 11:33

Your Common Sense

156,878
40
214
345

Thank you for your answer. Is it "insecurer", when i pass the variables in the "->execute" method instead of using "->bindValue"? – mr_app Mar 19 '13 at 12:15
1

Nope, they are the same. – Your Common Sense Mar 19 '13 at 12:26
Thank you. Is there a way to print the final sql-string for debugging? – mr_app Mar 19 '13 at 12:28
Should I expect significant difference in performance between single insert statement used with and without prepared statement? – triclosan Jan 20 '16 at 15:00

score 1 · Answer 2 · answered Mar 19 '13 at 11:31

1

Prepared Statement is mostly used for avoiding SQL injection done by hackers and any malicious program.

answered Mar 19 '13 at 11:31

Yogesh Suthar

30,424
18
72
100

herefor i can use ::escapeString, or??? – mr_app Mar 19 '13 at 11:35
1

There is no need to use `escapeString` because prepared statement is doing it by default. – Yogesh Suthar Mar 19 '13 at 11:36

Prepared statements vs. escaping strings in PHP SQLite3

2 Answers2