PHP/SQL Code Question

[netsider]

function insert_into($field, $value, $TABLE, $database){
$db = new PDO($database);
// $query = 'INSERT INTO ' . $TABLE . '(' . $field. ') VALUES (' . $value . ');'; First way
$query = 'INSERT INTO ' . $TABLE . '(' . $field. ') VALUES (' . '"' . $value . '"' . ');'; Second way
// echo $query;

$db->exec($query); // Value needs to be in parenthesis, but when it is, I can't insert $val (below).
$db = NULL;
}

I was experimenting with using SQLite as a database instead of MySQL and made the above function, which is then called (twice) using the code below:

if (isset($_POST)){ // If submit pressed
if (isset($_POST['submit-add'])){
$one = filter_input(INPUT_POST, "one");
insert_into('Name', $one, "Russ", 'sqlite:russ.db');
}}
$val = '"Red", "30"';
insert_into('Name, Age', $val, "Russ", 'sqlite:russ.db');

Currently, the uncommented line that sets the $query variable in the function makes it include double quotes around $value because it's the only way the first "insert into" query works. The commented line that sets the $query variable doesn't include double quotes, but is the only way I can get the second "insert into" query to successfully execute. Obviously, you see my dilemma - I can't get one (single) query to work for both the $one variable, and the $val variable. Each value seems to need double quotes (or single quotes, like in the 2nd query below this paragraph) around it, or it won't successfully enter into the database. Is there anyway to make a single query that'll work with both variable's ($one, and $val)? Sorry if any of this may seem confusing at first... I did try to provide enough information, however.

Before this, my original queries were:
// $query = '"INSERT INTO $TABLE ($field) VALUES ($value);"'; This works for the $val query, but not the $one query.
// $query = '"INSERT INTO $TABLE ($field) VALUES ('$value');"'; This works for the $one query/variable, but not the $val query/variable.

Thanks.. any help would be appreciated...

Edit: Assume there is a submit button present, and it has been pressed (just so nobody thinks this is all the code).

 

[x0dyssey]

Are you able to create a stored procedure that can handle the insert logic?

 

[hofan41]

You should not be trying to execute queries directly in pdo for many reasons. Use a prepared statement. http://php.net/manual/en/pdo.prepared-statements.php

 

[hofan41]

Also, the reason your single/double quotes work on certain values is probably because you are not properly escaping the single/double quotes.

For example, if your sql statement used single quotes, and the value you are trying to insert contained single quotes, you need to escape that character.

There are several ways to include quote characters within a string:

A “'” inside a string quoted with “'” may be written as “''”.

A “"” inside a string quoted with “"” may be written as “""”.

Precede the quote character by an escape character (“\&#8221.

A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside a string quoted with “'” needs no special treatment.

 

[netsider]

Are you able to create a stored procedure that can handle the insert logic?

Yeah... this doesn't really help me

 

[netsider]

You should not be trying to execute queries directly in pdo for many reasons. Use a prepared statement. http://php.net/manual/en/pdo.prepared-statements.php

Thanks for this.

Also, I thought escaping was only necessary when using special characters... I didn't know it was to be used in query's at all, but I can see why, now. It just didn't occur to me...

I appreciate the help.

 

[jiminator]

you need to separate the sql from the string handling.
Create a string containing your sql statement and print it. if it is not correct then fix it.
Otherwise forward to SQL or do the compile thingy.

 

[netsider]

Are you able to create a stored procedure that can handle the insert logic?

Just out of curiosity, can you explain what you were asking? It seems unrelated to my question, but if it's not, please explain so I can better understand the terminology/language? If it is unrelated, just ignore this reply.

The only thing I can think of is that you're asking if I'm using PDO prepared statements, like some of the other people asked...

Thanks

 

[jiminator]

SQL allows you to have functions and procedures directly inside the database. Functions just return values. Stored procedures can create or modify records. From PHP you would call these functions. You would use a SQL tool to look at the records and create these stored procedures. Then in your program you would do something like:

begin
blah(x,y,z);
end;

If you needed changes afterward you would just modify the stored procedure - unless you needed to add parameters or manipulate them before passing them

oracle makes a free tool for their databases, called sqldeveloper, there is also pl/sql developer which costs money - and some free tools that are not as powerful.