• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

Need help with turning array values to a string...

hi all...
i'm trying to figure out how to take an array and it's values and turn it into one big string in order to insert info into a database. Rather than writing query for every time someone updates or inserts info, i've created a function in which to do this for me. the array holds both the column names of the table and the values in which to be inserted...
EXAMPLE:
$fields['user_id'] = $userID;
$fields['name'] = $_POST['fullname'];
$fields['email'] = $_POST['Email'];

the function uses a foreach loop as such:

foreach($fields as $k => $z){            
      $sql_array[] = ' '.$k.' = \''.$z.'\'';
      echo implode(',', $sql_array);                  
}

So my question is, one how do i get the $sql_array into one string, and two, how can i access that string outside of the foreach loop? i have tried echoing out this:

echo 'INSERT INTO `myTable` SET '.implode(',', $sql_array);

but it returns it every time it loops through...so i would have three queries (if the array length is three) rather than one...
0
dawgpack
Asked:
dawgpack
  • 4
  • 4
  • 2
1 Solution
 
ebosscherCommented:
well, first off the INSERT INTO would not be used with the SET statement...
is this for UPDATEs or INSERTs?

if it's for inserts you could use the loop to do the following:

function CreateSqlInsert($tableName, $values, $stringFields)
{
    $columNames = '';
    $columValues = '';
    $isFirst = true;

    foreach($values as $key => $value)
    {
        $columNames = ($isFirst ? '' : ', ') . $key;
        $columnValues = ($isFirst ? '' : ', ') . ($stringFields[$key] ? "'", '') . $value . ($stringFields[$key] ? "'", '');
        $isFirst = false;
    }

    return 'INSERT INTO ' . $tableName . ' (' . $columNames . ') VALUES (' . $columnValues . ')';
}

for updates you would need a different routine

function CreateSqlUpdate($tableName, $values, $stringFields)
{
    $sql = 'UPDATE ' . $tableName . ' SET ';
    $first = true;

    foreach($values as $key => $value)
    {
        $sql .= ($isFirst ? '' : ', ') . $key . ' = ' . ($stringFields[$key] ? "'" : '') . $value . ($stringFields[$key] ? "'" : '');
    }

    return $sql;
}

in both cases the $stringFields is an array containing the fields for the table and a boolean value indicating if it is a string column or not (basically if it needs quotes)
0
 
ebosscherCommented:
dude, i helped because i'm a huge geek, but if you want more people to look at your stuff in the future it's a good idea to up the points...
0
 
ahoffmannCommented:
>        $sql_array[] = ' '.$k.' = \''.$z.'\'';
I guess you meant
          $sql_array[] = ' '.$k.' = '''.$z.'"';

then you can use your array as string if you write the implode() ouside the loop:

   $str=implode(',', $sql_array);
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ahoffmannCommented:
BTW, you're aware that your code is vulnerable to XSS, SQL injection, and probably most other attacks, are you?
0
 
dawgpackAuthor Commented:
thank you to both... i was not aware of any attacks... i am still learning and so i'm open to any suggestions either of you might have... how am i vulnerable? what can i do to not be?

ebosscher, thanks for the input and suggestion to up the points... i was just going off of how easy i thought the question was...i didn't realize that people would look more at it if it was worth more, though that makes perfect sense...
0
 
dawgpackAuthor Commented:
oh and i had one quick question in response to the functions ebosscher provided... when using the update function, i will need to update the table for a specific individual... so i will need to just ad this to to the end of the sql statement: ." WHERE ".$table.user_id." = ".$userID; and pass the userID val through the function? correct?
0
 
dawgpackAuthor Commented:
...and another thing... something is wrong with this syntax... in the CreateSqlInsert function, in the foreach loop:
$columnValues = ($isFirst ? '' : ', ').($stringFields[$key] ? "'", '').$value.($stringFields[$key] ? "'", '');

the "," is what is causing the error... i'm not sure what it should be instead... i'm not familiar with this syntax or method... i tried using the ":" instead but then it does not work...
0
 
ahoffmannCommented:
> how am i vulnerable?

fullname=%
fullname=foo' or 'x'='x
fullname=foo"'><script>alert('XSS')</script>

> what can i do to not be?
check each and every input to your script against a whitelist, something like:

preg_replace(/[^a-zA-Z0-9_]*/g, '', $fields['name']);
0
 
dawgpackAuthor Commented:
like i said, i am new to this... i don't understand what you are telling me... can you put it in layman's terms and not talk in code (pun intended)?!
0
 
ahoffmannCommented:
> fullname=foo"'><script>alert('XSS')</script>
fullname is your form's variable, anything behind the = is it's value
as you insert that value verbatime into yur string variable and later print it to the page, ther will be a
   <script>alert('XSS')</script>
somewher which pops up a javascript alert window

> fullname=foo' or 'x'='x
fullname is your form's variable, anything behind the = is it's value
as you use this value verbatim in your SQL statement and your don't use prepared statements. it will change your SQL query, probably not what you want/expect ;-)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now