Creating a site search engine with SQL injection prevention

[afxsguy]

@Shrapnel_N5

On using htmlspecialchars() to convert quotes… “No. You’ve lack of practice. Try to do it and look. No entity you’ve found. I’s like quotes in the query. Exists only in the form’s field.”

Looking at the database, I still see: "test" in the field. But you are right, when displaying the product page as HTML, the "'s are converted by the browser back to literal quote characters. Even the edit product page I have created with the input box converts it to literal quote characters. So having HTML entities like "'s stored in my database fields is what I want then right?

@Shrapnel_N5

On URL injection again… “Amazing.”

I guess it doesn’t matter, as long as strings are escaped and numbers are validated before going to SQL right? My thick head.

[Dan_Grossman]

Probably not. It limits what you can do with the data in your database for purposes other than displaying on a webpage. Searching, for example.

I’d not be converting input to HTML entities before putting them into a database. Do that at display time, not storage time.

@afxsguy

I guess it doesn’t matter, as long as strings are escaped and numbers are validated before going to SQL right? My thick head.

When I said “There’s no difference between the address bar and a form”, the conclusion to draw was that you treat them both the same, not “So if a variable is being passed through a URL, it does NOT need to be escaped?”. Both are user input, both populate $_GET or $_POST, you escape user input before putting it into a query.

[system]

No.
once you submit a form with value=“test "test" test” you’d have
test “test” test
on the PHP side

[afxsguy]

Oh I see. Yes, HTML entities in database would be bad for my search engine. So I only need to convert to HTML entities on display side. BUT, only when displaying in my forms; ie) putting the variable back into an input field for an edit form? It’s okay to have literal quotes between (<p>test “test” test</p>) paragraph tags, as long as my HTML header allows for it right?

EDIT: Nevermind. I guess I am wrong because if they start using characters like <, they are messing with HTML output. Unless I want to give my client the ability to enter HTML tags or code, then I really should be converting the special characters to HTML entities before displaying the text. ALSO, would I be right in saying that if I do want my client to have the ability to enter HTML tags into his text fields, that I should be writing logic that parses his code and decides whether his tags are legitimate?

[hash]

Yes, you should be writing logic to deal with this, bbcode is one method, notice what this forum does for bold etc.

@Shrapnel_N5

I begin to understand enormous advantage of prepared statements.
Because one have no possibility to think of data source, because they have only one way to do. Right way. No need to think - no possibility to loss

Just wanted to highlight this. I’d strongly encourage you to move away from mysql and look at mysqli or pdo, and the use of [URL=“http://www.php.net/manual/en/pdo.prepared-statements.php”]prepared statements.

[afxsguy]

@hash

Yes, you should be writing logic to deal with this, bbcode is one method, notice what this forum does for bold etc.

Cool thanks. I have no plans of using this unless a client insists they have control over formatting. When it comes time I’ll use bbcode; looks like a great idea.

@hash

Just wanted to highlight this. I’d strongly encourage you to move away from mysql and look at mysqli or pdo

I just looked into this, and I wouldn’t be moving away from mysql itself, but the drivers that PHP uses to interact with mysql, correct?

@hash

and the use of prepared statements.

In that prepared statements article it says “The parameters to prepared statements don’t need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).” …so this doesn’t change anything about what I’ve learned about string escaping, or validation of other variable types, right? If this is true, what are the advantages security-wise? Am I missing something?

@Shrapnel_N5

As though mysql_real_escape_string deals only with string literals, all other parts of the query require special attention. Here goes the real injection prevention.
LIMIT arguments must be cast to integer by some function or math operations.
identifiers (table and field names) must be hardcoded in the script and used only from that source, for example:

$order_fields=array("name","price","qty");
if (isset($order_fields[$_GET['order']])) $orderby=$order_fields[$_GET['order']];
else $orderby=$order_fields[0]];
$query="SELECT * FROM `table` ORDER BY `$orderby`";

Same for operators and functions.

Shrapnel, I think I understand everything you’ve told me now (thank you guys so much for your patience with me), but what do you mean by “limit arguments”? I am not sure I understand what you were trying to say in that post.

PS. Dan, I want to thank you as well for being patient with me. I’ve learned so much from all you guys here.

[Dan_Grossman]

@afxsguy

In that prepared statements article it says “The parameters to prepared statements don’t need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).” …so this doesn’t change anything about what I’ve learned about string escaping, or validation of other variable types, right? If this is true, what are the advantages security-wise? Am I missing something?

As the text you quoted says, if you use prepared statements, you can be sure no SQL injection will occur. You do not ever have to escape user input if you use prepared statements. It is done for you. The security advantage is that the computer never forgets to escape the input, where you might if you weren’t using prepared statements and had to do it yourself.

[afxsguy]

Is the mysqli driver required to make prepared statements?

[Dan_Grossman]

Either mysqli or PDO. I’d recommend PDO.

[afxsguy]

I just did a phpinfo() check, and I think I have mysqli and PDO enabled. Am I correct? http://www.horizonsolutions.org/phpinfo.php

Are there any situations where I should ever NOT use prepared SQL statements? It sounds like this is the best way to prevent SQL injection.

PS. Why PDO over mysqli? I’m not partial to either, but I am just curious why you prefer it?

[system]

what do you mean by “limit arguments”?

There is LIMIT operator in MySQL
It has 2 arguments.
You can’t use escaping/quotes to sanitize them.

you can be sure no SQL injection will occur

With exception I noted above:
Prepared statements can’t help with identifiers/operators
Not sure about LIMIT parameters, always were curious about it.

[hash]

@Shrapnel_N5

Not sure about LIMIT parameters, always were curious about it.

Yes, you can, but no point when you can (int) them??

$st = $db->prepare("SELECT * FROM test LIMIT ?, ?");
$start = 0;
$limit = 10;
$st->bindParam(1, $start, PDO::PARAM_INT);
$st->bindParam(2, $limit, PDO::PARAM_INT);
$st->execute();

@afxsguy

PS. Why PDO over mysqli? I’m not partial to either, but I am just curious why you prefer it?

Some reasons you may want to go with pdo over mysqli

• Abstraction layer
• Defacto standard for php5
• More features than mysqli
• Advisors with 20k posts recommend it

[afxsguy]

@hash

• Advisors with 20k posts recommend it

Cannot argue with that!

Are there any situations where I should ever NOT use prepared SQL statements, and instead running SQL queries using the standard mysql_query() function? Or does PDO COMPLETELY replace that?

Also, I’ve been googling PDO and I just read this in one of the articles: “The inherent security in using prepared statements sounds great, but developers should not let PDO and other abstraction layers/prepared statement implementations lull them into a false sense of security. Untrusted data should always be validated and sanitised, PDO is just another line of defense.” …now I am not at all questioning you guys, but just wondering if it is a good idea to validate variable data types and use mysql_real_escape_string() on string variables, even though you guys said it’s not necessary with prepared statements? Is that a waste of my time, or is it a good defense to do both? Will quotes be \\“double\\” escaped? Or will PDO ignore quotes that have already been escaped?

…I know we’ve went over this, but after learning about prepared statements, I’m trying to learn how to put up a proper defense without going overkill (like what I came up with in the last thread I started, ouch). I’m not that good of a programmer yet as PHP is my first real language; so once again I thank you all for teaching me this stuff!!!

[system]

It is stupid article, just put it from your mind.
Although you are still in danger because you don’t understand mysql_real_escape_string() nor prepared statements.

[afxsguy]

@Shrapnel_N5

Although you are still in danger because you don’t understand mysql_real_escape_string() nor prepared statements.

I’ve obviously been in danger from the start, which is why I’m seeking your advice.

I can’t help but being confused when half the articles I read are considered silly by your means. For the most part I trust you guys more than I do the articles I read because for the most part you guys have been explaining why and why not what I am reading is silly. I AM still learning. However, I am just trying to do my research. I don’t mean to be disrespectful by any means, but what more do you expect from a new programmer who recently did not even know how to echo “Hello World”?

@Shrapnel_N5

It is stupid article, just put it from your mind.

Fair enough. SO let’s see if I understand this correctly:

1. It took me a while until I understood what “escaping” even meant, but now I know all it does is tell the SQL statement that the character being escaped is to be used in a literal sense. This is important so that things like quotes do not allow the attacker to expand or change the intent of a query.

2. mysql_real_escape_string() is one escaping method that is very safe, as long as i validate all of my other variable types properly.

3. prepared statements is a different method that is very safe as it escapes data for you and ensures that variable data is bound to a prewritten SQL statement so that the intent of the query cannot be changed by an attacker.

4. there is no sense in using BOTH the mysql_real_escape_string() method or the prepared statement method for extra protection, because they both do the same thing but in different ways. using mysql_real_escape_string() is a very MANUAL method, so the programmer must remember to properly escape/validate every bit of data before the query is executed. using prepared statements is a bit more AUTOMATIC, and also allows for a faster running experience when a specific query needs to be executed over and over (because the statement only has to be “prepared” once). because of this, using prepared statements has a pretty huge advantage over doing things “manually” with mysql_real_escape_string().

5. lastly, i still have to be careful about LIMIT operators where there is 2 arguments because quote escaping will not sanitize them; in which case neither method above would not be safe.

please correct me if i am wrong on any of the above. if i still don’t understand, please shoot me now :injured:

[system]

Now you grow up.

prepared statements is a different method that is very safe as it escapes data

It is not quite truth. But one cannot blame you for this.
It does not escape anything. You can realize this from the fact that no quotes used - so, nothing to escape.
It has different method to handle data. Your data being sent separate from the query, so that the intent of the query cannot be changed by an attacker.

in which case neither method above would not be safe.

wrong.
As shown in the hash’s example, PDO covers LIMIT parameters too, they are o.k.

The only thing left not covered by your checklist is fieldnames.

[afxsguy]

@Shrapnel_N5

Now you grow up.

Like I said, I did not mean disrespect in any way. I know I am still very much learning, and UNTIL I understand this fully, my code will be dangerous. I’m asking for your help, and I appreciate you taking the time to help me understand.

@Shrapnel_N5

(on prepared statements)… It does not escape anything. You can realize this from the fact that no quotes used - so, nothing to escape.

but didn’t Dan Grossman say earlier…

@Dan_Grossman

You do not ever have to escape user input if you use prepared statements. It is done for you. The security advantage is that the computer never forgets to escape the input, where you might if you weren’t using prepared statements and had to do it yourself.

This is why I thought prepared statements DO escape the data. So they do not?

@Shrapnel_N5

The only thing left not covered by your checklist is fieldnames.

Right, they need to be hard coded rather than dynamically inserted data.

[system]

So they do not?

No, they don’t.
One can say they being escaped in the meaning of they are safe. But there is no escaping literally.
It’s the way how prepared statements works: no escaping but separation from the query.

[afxsguy]

@Shrapnel_N5

One can say they being escaped in the meaning of they are safe. But there is no escaping literally.

I guess this does not matter whether they are escaped literally or not; as long as I know that escaping is not necessary when using prepared statements is what is important.

[system]

We, programmers, call it “obfuscation”. When the real meaning of the thing being hidden under false name.
It makes things harder, but one can live with it