$_POST and Building SQL Strings

[john]

All:

I'm a long-time developer, new to PHP....

Is there an idiom used in PHP to construct SQL statments from $_POST
data?

I would guess that in many applications, the data read from $_POST are
used to build SQL statements. Certainly, we can do the following:

$email = $_POST['email']
$sql = "insert ... values ('$email')..."

However, pulling out each variable from the $_POST array seems
awkward.

The problem with constructing a string comes in due to the fact that
you often need to quote strings in the SQL statement, e.g, $sql =
"insert...values('$_POST['email']..)" There doesn't seems to be a
combination of single and double quotes that work.

Is there a standard way people tend to build SQL strings from $_POST
(or $_GET) data in PHP?

Thanks,
John
jpuopolo

[Aerik]

On May 3, 5:02 pm, john <[e-mail]:
<snip>

>
The problem with constructing a string comes in due to the fact that
you often need to quote strings in the SQL statement, e.g, $sql =
"insert...values('$_POST['email']..)" There doesn't seems to be a
combination of single and double quotes that work.

Is there a standard way people tend to build SQL strings from $_POST
(or $_GET) data in PHP?

I'll be interested to see other answers to this too. I like to mangle
your post data first by looping through the $_POST and building your
$fields and $values string, all the while checking for valid field
names and escaping your strings appropriately. Then just do this:

$sql = "INSERT INTO mytable ($fields) VALUES ($values)";

Aerik

[john]

On May 3, 8:08 pm, Aerik <[e-mail]:

On May 3, 5:02 pm, john <[e-mail]:
<snip>

The problem with constructing a string comes in due to the fact that
you often need to quote strings in the SQL statement, e.g, $sql =
"insert...values('$_POST['email']..)" There doesn't seems to be a
combination of single and double quotes that work.

Is there a standard way people tend to build SQL strings from $_POST
(or $_GET) data in PHP?

I'll be interested to see other answers to this too. I like to mangle
your post data first by looping through the $_POST and building your
$fields and $values string, all the while checking for valid field
names and escaping your strings appropriately. Then just do this:

$sql = "INSERT INTO mytable ($fields) VALUES ($values)";

Aerik

Aerik et al:

Interestingly, this seems to work...(I just tested this before I saw
your (very quick - thanks!) reply:

$fruit = array('a' ='apricot', 'b' ='banana');
$s = "insert into food(x,y) values ('$fruit[a]', '$fruit[b]')";
print $s;
// prints: insert into food(x,y) values ('apricot', 'banana')

This constructs the proper SQL statement. I was having a difficult
time since I thought I needed to surround the index (a and b above)
with some type of quotes. Apparently, PHP can use the non-quoted
index, which solves the simple problem of using $_POST[index], even
when index is a named index into the array. Cool....

jpuopolo

[=?ISO-8859-15?Q?Iv=E1n_S=E1nchez_Ortega?=]

john wrote:

However, pulling out each variable from the $_POST array seems
awkward.

Unless you program a framework just for that, it's the way to go.

The problem with constructing a string comes in due to the fact that
you often need to quote strings in the SQL statement

You *always* have to quote strings in SQL.

, e.g, $sql = "insert...values('$_POST['email']..)" There doesn't seems to
be a combination of single and double quotes that work.

Re-read the PHP manual, chapter on string expansion: whenever you put an
array element inside a double-quoted string, you must enclose it with curly
braces.

Is there a standard way people tend to build SQL strings from $_POST
(or $_GET) data in PHP?

Yes: *always* escape the variables (or at least, check them):

$email = mysql_real_escape_string($_POST['email']);
$name = mysql_real_escape_string($_POST['name']);
$age = (int) $_POST['age'];

$sql = "insert into foobar values ('$name','$email',$age)";
Do this, and you'll never worry about SQL injections.

--
----------------------------------
Iván Sánchez Ortega -ivansanchez-algarroba-escomposlinux-punto-org-

http://acm.asoc.fi.upm.es/~mr/ ; http://acm.asoc.fi.upm.es/~ivan/
MSN:i_*************************@hotmail.com
Jabber:iv*********@jabber.org ; iv*********@kdetalk.net

[Jerry Stuckle]

Iván Sánchez Ortega wrote:

john wrote:

>However, pulling out each variable from the $_POST array seems
awkward.

Unless you program a framework just for that, it's the way to go.

>The problem with constructing a string comes in due to the fact that
you often need to quote strings in the SQL statement

You *always* have to quote strings in SQL.

>, e.g, $sql = "insert...values('$_POST['email']..)" There doesn't seems to
be a combination of single and double quotes that work.

Re-read the PHP manual, chapter on string expansion: whenever you put an
array element inside a double-quoted string, you must enclose it with curly
braces.

>Is there a standard way people tend to build SQL strings from $_POST
(or $_GET) data in PHP?

Yes: *always* escape the variables (or at least, check them):

$email = mysql_real_escape_string($_POST['email']);
$name = mysql_real_escape_string($_POST['name']);
$age = (int) $_POST['age'];

$sql = "insert into foobar values ('$name','$email',$age)";
Do this, and you'll never worry about SQL injections.

And in addition, you need to validate the data before you put it into
the database. Ensure, for instance, that your numeric values are indeed
numeric.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[blessblessbless]

does this not work?
$sql = "insert ... values ('".$_POST['email']."')...";

in case it is not readable:
('".$_POST['email']."') = (' ". $_POST['email'] . " ')
was that even the question?

[Geoff Muldoon]

Iván Sánchez Ortega says...

You *always* have to quote strings in SQL.

Unless you're using a database that supports bind variables.

GM

[Norman Peelman]

Iván Sánchez Ortega wrote:

john wrote:

>However, pulling out each variable from the $_POST array seems
awkward.

Unless you program a framework just for that, it's the way to go.

>The problem with constructing a string comes in due to the fact that
you often need to quote strings in the SQL statement

You *always* have to quote strings in SQL.

>, e.g, $sql = "insert...values('$_POST['email']..)" There doesn't seems to
be a combination of single and double quotes that work.

Re-read the PHP manual, chapter on string expansion: whenever you put an
array element inside a double-quoted string, you must enclose it with curly
braces.

Not true...

"insert ... values('$_POST[email]') ...";

works just fine (as it should). Constants are NOT checked for when used
in this manner. However, using curly braces causes Constants to be
checked thereby re-introducing the need for single quotes around key names.

Curly braces are really only needed when accessing multi-dimensional
arrays in this manner:

"insert ... values('{$arr['key1']['key2']}') ... ";

or

"insert ... values('{$arr[0][1]}') ... ";

....just pick a good method and stick with it. Me personally, I hate
resorting to string concatination if at all possible:

<-- from bl*************@gmail.com -->

>does this not work?
$sql = "insert ... values ('".$_POST['email']."')...";

....and in his/her own words

>in case it is not readable:
('".$_POST['email']."') = (' ". $_POST['email'] . " ')

Norm

>Is there a standard way people tend to build SQL strings from $_POST
(or $_GET) data in PHP?

Yes: *always* escape the variables (or at least, check them):

$email = mysql_real_escape_string($_POST['email']);
$name = mysql_real_escape_string($_POST['name']);
$age = (int) $_POST['age'];

$sql = "insert into foobar values ('$name','$email',$age)";
Do this, and you'll never worry about SQL injections.

[Jerry Stuckle]

Geoff Muldoon wrote:

Iván Sánchez Ortega says...

>You *always* have to quote strings in SQL.

Unless you're using a database that supports bind variables.

GM

That's not standard SQL, though. Standard SQL requires single quotes
around strings used for data (but not table/column names, etc.).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Rami Elomaa]

"john" <pu*****@gmail.comwrote in message
news:11**********************@n76g2000hsh.googlegr oups.com...

On May 3, 8:08 pm, Aerik <[e-mail]:

>On May 3, 5:02 pm, john <[e-mail]:
<snip>

The problem with constructing a string comes in due to the fact that
you often need to quote strings in the SQL statement, e.g, $sql =
"insert...values('$_POST['email']..)" There doesn't seems to be a
combination of single and double quotes that work.

Is there a standard way people tend to build SQL strings from $_POST
(or $_GET) data in PHP?

I'll be interested to see other answers to this too. I like to mangle
your post data first by looping through the $_POST and building your
$fields and $values string, all the while checking for valid field
names and escaping your strings appropriately. Then just do this:

$sql = "INSERT INTO mytable ($fields) VALUES ($values)";

Aerik

Aerik et al:

Interestingly, this seems to work...(I just tested this before I saw
your (very quick - thanks!) reply:

$fruit = array('a' ='apricot', 'b' ='banana');
$s = "insert into food(x,y) values ('$fruit[a]', '$fruit[b]')";
print $s;
// prints: insert into food(x,y) values ('apricot', 'banana')

This constructs the proper SQL statement. I was having a difficult
time since I thought I needed to surround the index (a and b above)
with some type of quotes. Apparently, PHP can use the non-quoted
index, which solves the simple problem of using $_POST[index], even
when index is a named index into the array. Cool....

It works, but it's wrong. This works as well but is also wrong: $_POST[a].
What php sees is a constant named a, then looks for that constant from
defined variables and does not find it. Then it assumes it was a "bare
string", a string without quotes. You'll find that this will work just as
well:
echo hello;

It's the same thing, it looks for constant hello, doesn't find it and
finally assumes you meant 'hello'.

The "proper" way might be like this:
$s = "insert into food(x,y) values ('{$fruit['a']}', '{$fruit['b']}')"; The
curly braces can be used to isolate variables inside a string and they help
php understand what you meant to write...

http://www.php.net/manual/en/language.types.string.php
Read the "Complex (curly) syntax" chapter.

--
Ra*********@gmail.com

"Good tea. Nice house." -- Worf

[Umberto Salsi]

john <pu*****@gmail.comwrote:

However, pulling out each variable from the $_POST array seems
awkward.

Here are some ideas:

1. Naive solution:

function string_field($s){ return "'".my_db_escape_str($s)."'"; }
function int_field($i){ checks_proper_int($i); return $i; }

$sql = "INSERT INTO myTable (f1, f2, f3) VALUES ("
. string_field( $_POST['f1'] )
. int_field( $_POST['f2'] )
. string_field( $_POST['f3'] ) .")";

my_db_query($sql);
2. Improving the point 1 adding the validation. A global string $err is
set with the errors we found:

$err = "";

function string_field($name, $maxlen)
{
$value = trim( $_POST[$name] );
$GLOBALS['err'] .= checks_proper_char_encoding($value);
if( mb_strlen($value) $maxlen )
$GLOBALS['err'] .= "Field $name too long, max $maxlen chars allowed. ";
return "'".my_db_escape_str($s)."'";
}

function int_field($name, $sign_allowed, $maxdigitsintpart, $maxdigitsfracpart)
{ ... }

$sql = "INSERT INTO myTable (f1, f2, f3) VALUES ("
. string_field( 'f1', 20 )
. int_field( 'f2', 9, 2 )
. string_field( 'f3', 20 ) .")";

if( strlen($err) == 0 ){
my_db_query($sql);
} else {
echo "ERROR: $err";
}
3. Using a sort of "SQL-printf" function. If you know how the printf()
function works, this should look familiar:

function field($fmt, $name)
{ ... }

$sql = "INSERT INTO myTable (f1, f2, f3) VALUES ("
. field( "20s", 'f1' )
. field( "9.2f", 'f2' )
. field( "10s", 'f3' ) .")";

if( strlen($err) == 0 )...
4. Using the interesting feature of the PHP know as "functions with variable
number of arguments". In this case the arguments are the name of the table
and a "field descriptor" for each field containing the type descriptor (as
above) and the field name. A global variable $err is set with the errors
found validating every field. The function returns the complete SQL query
ready to be submitted to the DB:

function build_sql_insert( $table /*, field descriptors here */)
{...}

$sql = build_sql_insert("myTable", "20s f1", "9.2i f2", "10s f3");

if( strlen($err) == 0 )...

Similar functions performs SELECT and UPDATE, but some more arguments can
be required for the WHERE part of the SQL request.

Regards,
___
/_|_\ Umberto Salsi
\/_\/ www.icosaedro.it

[gosha bine]

On 04.05.2007 02:02 john wrote:

All:

I'm a long-time developer, new to PHP....

Is there an idiom used in PHP to construct SQL statments from $_POST
data?

I would guess that in many applications, the data read from $_POST are
used to build SQL statements. Certainly, we can do the following:

$email = $_POST['email']
$sql = "insert ... values ('$email')..."

However, pulling out each variable from the $_POST array seems
awkward.

The problem with constructing a string comes in due to the fact that
you often need to quote strings in the SQL statement, e.g, $sql =
"insert...values('$_POST['email']..)" There doesn't seems to be a
combination of single and double quotes that work.

Is there a standard way people tend to build SQL strings from $_POST
(or $_GET) data in PHP?

Thanks,
John
jpuopolo

Hi there

the most correct and headache-free way to build an SQL statement is to
use prepared statements. If you're on php5, it's strongly recommended to
use PDO [http://www.php.net/manual/en/ref.pdo.php] that has this feature
out of the box:

$st = $db->prepare('INSERT INTO ... VALUES(?, ?)');
$st->execute(array($_POST['name'], $_POST['email']));

For older php versions you can use libraries that emulate the same
functionality: AdoDB, PEAR::MDB etc.
--
gosha bine

extended php parser ~ http://code.google.com/p/pihipi
blok ~ http://www.tagarga.com/blok

[Toby A Inkster]

blessblessbless wrote:

does this not work?
$sql = "insert ... values ('".$_POST['email']."')...";

$sql = sprintf("INSERT INTO people VALUES ('%s', '%s', %d);"
,pg_escape_string($_POST['name'])
,pg_escape_string($_POST['email'])
,(int)$_POST['age']
);

--
Toby A Inkster BSc (Hons) ARCS
http://tobyinkster.co.uk/
Geek of ~ HTML/SQL/Perl/PHP/Python/Apache/Linux

[Geoff Berrow]

Message-ID: <sl*******************@murphy.redbrick.dcu.iefro m David
Gillen contained the following:

>You can insert the following snippet
foreach($_POST as $key=>$val)
{
$$key = $val;
}

Never trust user supplied data.
But (for mysql) you could do:
foreach($_POST as $key=>$val)
{
$$key = mysql_real_escape_string($val);
}
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[john]

On May 3, 8:02 pm, john <[e-mail]:

All:

I'm a long-time developer, new to PHP....

Is there an idiom used in PHP to construct SQL statments from $_POST
data?

I would guess that in many applications, the data read from $_POST are
used to build SQL statements. Certainly, we can do the following:

$email = $_POST['email']
$sql = "insert ... values ('$email')..."

However, pulling out each variable from the $_POST array seems
awkward.

The problem with constructing a string comes in due to the fact that
you often need to quote strings in the SQL statement, e.g, $sql =
"insert...values('$_POST['email']..)" There doesn't seems to be a
combination of single and double quotes that work.

Is there a standard way people tend to build SQL strings from $_POST
(or $_GET) data in PHP?

Thanks,
John
jpuopolo

All:

Thank you all for these timely and excellent responses. I am grateful.

John
jpuopolo

[Roy Kaldung]

Iván Sánchez Ortega wrote:

john wrote:

>However, pulling out each variable from the $_POST array seems
awkward.

Unless you program a framework just for that, it's the way to go.

>The problem with constructing a string comes in due to the fact that
you often need to quote strings in the SQL statement

You *always* have to quote strings in SQL.

I agree, but I'm proposing to use a database abstraction layer like PDO
in conjunction with prepared statements to avoid quoting input and
prevent SQL incjections.

hth,
Roy

[Toby A Inkster]

Geoff Berrow wrote:

David Gillen wrote:

>You can insert the following snippet
foreach($_POST as $key=>$val)
{
$$key = $val;
}

Never trust user supplied data.
But (for mysql) you could do:
foreach($_POST as $key=>$val)
{
$$key = mysql_real_escape_string($val);
}

That's no better really. Consider, we'll use $_GET as an example instead
of POST, as it's easier to illustrate, but the same principle applies:

http://example.com/foo.php?_SESSION[is_administrator]=1

or, say you log IP addresses into your database whenever someone edits
data...

http://example.com/foo.php?_SERVER[REMOTE_ADDR]=123.45.67.89

--
Toby A Inkster BSc (Hons) ARCS
http://tobyinkster.co.uk/
Geek of ~ HTML/SQL/Perl/PHP/Python/Apache/Linux

[Geoff Berrow]

Message-ID: <n5************@ophelia.g5n.co.ukfrom Toby A Inkster
contained the following:

>foreach($_POST as $key=>$val)
{
$$key = mysql_real_escape_string($val);
}

That's no better really. Consider, we'll use $_GET as an example instead
of POST, as it's easier to illustrate, but the same principle applies:

http://example.com/foo.php?_SESSION[is_administrator]=1

or, say you log IP addresses into your database whenever someone edits
data...

http://example.com/foo.php?_SERVER[REMOTE_ADDR]=123.45.67.89

Thanks for that Toby. It's always good to have an illustration of what
the potential problem might be.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/