best mysql syntax

[insei]

hi,

what is the best way of writing mysql queries?

example :


query = "UPDATE `some_table` SET something = '".$something."' WHERE (username = '".$username."' AND age = '".$age."' )";


i've seen different ways of doing this, what is the best to do this while preserving security and readablity?

[insei]

are these tags nesassary `some_table` , instead of just some_table

[insei]

$Query = "SELECT id, email, level FROM users WHERE username='$User' AND password='$Pass'";


this one is alot different then the one i first posted, is this less secure? =/

instead of

username = '".$username."'

it has

username = '$username'

[NogDog]

Using the "back tick" quotes around table or column names is only required if the name could clash with a reserved word or does not fit certain naming restrictions (e.g. has a space in the name). It never hurts to quote them that way, so many people make a practice of always quoting them just to be safe, consistent, and forward-compatible with any new reserved words or such.

Whether or not to use concatenation in PHP (the "." operator) or just using the variable directly within a double-quoted string literal is largely a stylistic choice. In very old versions of PHP there was some measurable performance gains from using concatenation, but these days that's not really an issue. I often use the sprintf() function in PHP to build query strings, especially if I need to sanitize the input values:

$query = sprintf(
"SELECT `col1`, `col2` FROM `table` WHERE `col` = '%s' AND `col2` = %d",
mysql_real_escape_string($some_value),
round($some_number)
);

[insei]

thank you for your answer, apriciate it

[insei]

yes, my sites :http://www.bargain-batteries.com and http://www.any-e-accessories.com are also used the same tech. you can go to see!

whats this?

[criterion9]

whats this?

Looks like spam. Did you click the icon flagging the post for review yet?

[Michael_G]

That works for short SQL Queries, but for longer queries if you put the SQL string all in one line it can sometimes be difficult to read and change. It may be more neat and easier to read to concatenate onto the query string over several lines of code, for example, in PHP you would do like this:

$query = "SELECT FirstName,";
$query .= "LastName,";
$query .= "Email,";
$query .= "Address ";
#query .= "FROM CustomerContact";

Likewise, in ASP .NET (C#) you would do like the following:

string Query = "";
Query = Query + "SELECT FirstName,";
Query = Query + "LastName,";
Query = Query + "Email,";
Query = Query + "Address ";
Query = Query + "FROM CustomerContact";

This is a little more typing, but when it comes time to change your query its a little more organized and easier to change, for reasons of maintainability it is a better format.

Michael G. Workman
michael.g.workman@gmail.com

[svidgen]

When you find yourself dealing with a lot of particularly complex queries, you might even find it most convenient to write some query builder routines (or helper routines):

function sanitize($v) {
if (is_array($v)) {
foreach ($v as $key => $value) {
$v[$key] = sanitize($value);
}
return $v;
} else {
// we rely on the our index/driver to ensure that magic quotes
// has not affected our data. So, we should just have to worry
// standard escaping at this point:
return mysql_escape_string($v);
}
} // sanitize()

function get_valid_fields($n, $db) {
if (!$db = connect_inventory()) {
return array();
}

if ($result = myquery("describe {$n}", $db)) {
$fields = array();
while ($row = my_fetch_assoc($result)) {
if ($row['Extras'] != 'auto_increment') {
$fields[] = $row['Field'];
}
}

return $fields;
} else {
return array();
}
} // get_valid_fields()


function build_set_clause($q, $v = false) {
// $f : fields
// $v : valid_fields

$fields = array();

if ($v && !is_array($v)) {
$v = get_valid_fields($v);
}

if (is_array($v) && sizeof($v) > 0) {
foreach ($v as $fn) {
if ($q[$fn]) {
$fields[$fn] = $q[$fn];
}
}
} else {
$fields = $q;
}

foreach ($fields as $k => $v) {
$fields[$k] = "{$k}='" . sanitize($v) . "'";
}

return join(',', $fields);
} // build_set_clause()

... in this example, get_valid_fields() is an breakage protection function used to prevent "extra" values in an array from being inserted into the table. So, the application may deal with an $accounts array and may store derived information in the array that doesn't necessary belong in the DB table. By providing the database.accounts tablename or an array defining the specific fields you want to filter by in the build_set_clause() call, I can just send in my $accounts array and rest assured that it will generate an appropriate SET clause for my update, despite having additional fields in it. This is more useful if you're working on a somewhat complex user-customizable application (like a CRM).

Typical applications will not require something like get_valid_fields(), but may benefit from having functions like build_set_clause(), sanitize(), build_where_clause(), and so forth.

[Shorts]

I like NogDog's method.

Personally go with a multiple line format:

$sql = DB()->select(
"SELECT t.row_id
FROM table_one t
INNER JOIN table_two r USING (row_id)
WHERE t.type=".DB()->clean($type)."
AND r.deleted=0
AND r.activated=1"
);

For reference, the DB() returns a statically declared $DB class inside it. The class has built in functionalities, e.g. printing out a formatted string to screen if a stored value show_sql is on. Also, did it this way so DB() can be called globally (via functions, classes, or on the global scope).

Also, I use the ".$value." since it's easier to spot values via my text editor (different colors). I do that for all strings. I use double quotes on strings for SQLs only, everything else is single quotes.

[NogDog]

And just to confuse the OP more, you could use the PHP "heredoc" syntax. :)

$value1 = mysql_real_escape_string($_POST['value_1']);
$val2 = (int) $_POST['id'];
// use "heredoc" syntax to quote string:
$query = <<<END
SELECT
a.col1,
a.col2,
b.col99
FROM
table1 AS a,
INNER JOIN table2 AS b ON a.id = b.fk_id
WHERE
a.col1 = '$value1' AND
b.col99 = $val2
ORDER BY a.col2
END;

// There must not be any white-space before "END;"
$result = mysql_query($query);

[insei]

woah thanks ^^ i got to check these out