ansaurus

Question

Seeking assistance with Escaping Data for MySQL queries

Answer 1

A:

Why can't you use mysql_real_escape_string?

You can also use a regexp to only allow certain characters that would be expected in a name

baloo 2010-05-24 15:03:09

Answer 2

+3 A:

You seem to already know that you should be using mysql_real_escape_string but I guess you don't know how to use. You need to apply it for each user supplied string you insert into your SQL. The following example should clarify this:

$sql = "INSERT INTO tablename VALUES ('', '" .
    mysql_real_escape_string($_SESSION['Member1FirstName']) . "', '" .
    mysql_real_escape_string($_SESSION['Member1LastName']) . "', '" .
    etc..

Or alternatively look into prepared statements and bind parameters for an easier (and faster) solution.

Mark Byers 2010-05-24 15:03:33

@Mark Byers - thanks for the input. I am thinking about running mysql_real_escape_string(var) at the time I actually store the variables into SESSION, then only running INSERT INTO tablename VALUES ('', '$_SESSION[Member1FirstName]'....) any issue with this? Also - what are the . "', '" . you use?

JM4 2010-05-24 15:37:53

JM4: While your suggeston would work it would be easy to forget to do the escaping, and hard to notice the error if you do forget. The `.` means string concatenation and `"', '"` is just an ordinary string. With prepared statements and bind_paramaters there is very little chance to make an error and the query will be turned into a query plan faster.

Mark Byers 2010-05-24 15:44:33

@Mark - is the following statement overkill? $_SESSION['F1FirstName'] = mysql_real_escape_string(htmlspecialchars($_POST['F1FirstName']));

JM4 2010-05-24 16:31:33

@JM4: You only need htmlspecialchars if you are displaying the string as html.

Mark Byers 2010-05-24 18:15:32

Answer 3

+2 A:

1) you're missing your closing single-quote and vars aren't replaced inside of single quotes.

2) mysql_real_escape_string is the answer, but try it with sprintf:

$sql = sprintf("INSERT INTO tablename VALUES ('', '%s', '%s', '%d' )",
               mysql_real_escape_string( $_SESSION['Member1FirstName']),
               mysql_real_escape_string( $_SESSION['Member1LastName']),
               $_SESSION['Member1ID']); // %d forced it as a digit

http://us2.php.net/manual/en/function.sprintf.php

Dan Heberden 2010-05-24 15:05:35

@ Dan - 1) was just basic sample code, not prod code but understood; 2) what is benefit of using sprintf if I'm already escaping?

JM4 2010-05-24 15:36:03

In this case it gives you quick access to force as number but the biggest reason is readability. You can keep your sql clean and easy to read for those ridiculously long expressions that can happen and keep your vars easily managed below. You can also make numbers have a set amount of leading zeros. All kinds of cool stuff :)

Dan Heberden 2010-05-24 15:42:11

ansaurus

tags:

views:

answers:

Seeking assistance with Escaping Data for MySQL queries

related questions