0

I am having trouble with SQL Management studio and I do not want to connect to this SQL server I want to make the data ready for my lines to be inserted in this database I have a text file with the lines of strings that I want to insert in sql server the line is like this:

You're Doing It Wrong!!,Mike Walsh,Intermediate

So it should be like this to be ready for sql server.

You''re Doing It Wrong!!,Mike Walsh,Intermediate

I also have this in lines:

Never Have to Say "Mayday!!!" Again

Is this one going to become a problem? Should I have any plan for it also?

I tried to use addslash and then replace the slash with the a single quote by doing:

  $str=",('".addslashes ($array[0])."')";
     $str=str_replace("\\","\'",$str);
     echo $str;

I did the comma and parenthesis for when I have insert to query in sql server the result of this one will be:

    ,('You\''re Doing It Wrong!!'),
,('Never Have to Say \'"Mayday!!!\'" Again'),

What did I do wrong here?

Nickool
  • 3,662
  • 10
  • 42
  • 72
  • Use prepared statements to insert your data. – chris85 Apr 25 '15 at 20:43
  • I'm not using mysql and I am just making ready my lines to be inserted in sql server – Nickool Apr 25 '15 at 20:45
  • My comment says nothing about mysql. Use the PDO driver and insert your data with a prepared statement, query separated from data. – chris85 Apr 25 '15 at 20:47
  • It is a bit unclear what the question is: would you like to know which characters need to be escaped or do you have problems with the regexp creating them? For the escape sequences, you might look at http://stackoverflow.com/questions/5139770/escape-character-in-sql-server . – lambda.xy.x Apr 25 '15 at 20:49
  • I don't want to do that, it is not a tremendous data and also if I even want I can't database is remote – Nickool Apr 25 '15 at 20:49
  • @lambda.xy.x I am she by the way, I want to have a regex that can handle the string for sql management studio , I wanted to add a single quote to the singe quote and as I said I have problem with that – Nickool Apr 25 '15 at 20:51
  • If you're doing it in the sql management studio than you're not using PHP. – chris85 Apr 25 '15 at 20:53
  • @chris85 data is for sql server and I am just playing with php in text file making it clean to go to sql server – Nickool Apr 25 '15 at 20:56

3 Answers3

2

You are approaching this problem in a wrong way: rather than preparing the string to be "pasted" into SQL Server's query, parameterize your SQL, and pass the string as a parameter. This way you wouldn't have to escape it at all, and the number of quotes or other special characters wouldn't matter either:

$sql = "INSERT INTO MyTable(id,name) VALUES (?,?)"
$params = array($someId, $name)
$sql_srv_query($db_conn, $sql, $params);
Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • I cannot use sql server I am having remote access and I cannot do it other way – Nickool Apr 25 '15 at 20:46
  • @Nickparsa The nice thing about parameterized queries is that it's very much bulletproof. You should try using prepared statement over a file, unless it's a one-time job. Missing a single corner case while generating "canned" SQL can be extremely costly, especially if someone with bad intentions and a good knowledge of your script has access to your source text file. – Sergey Kalinichenko Apr 25 '15 at 20:51
  • It is not a serious issue really, it is just a project which our professor sent us some data in a really bad format of excel sheet with so many typos and wrong stuff and it is not even related to the project we should only have this data. – Nickool Apr 25 '15 at 20:54
0

May be try this

$str = "You're Doing \"It Wrong!!,Mike Walsh,Intermediate";
$st = addslashes($str);

First of all you can replace " with \", then make addslashes

mcklayin
  • 1,330
  • 10
  • 17
0

Using prepared statements is the best way. If you insist on a regex way, you can double single quotes with preg_replace so that there is an even number of consequent single quotes:

''|(')

And replace with ''. See demo

Sample PHP code:

$re = "/''|(')/"; 
$str = "You're Doing It Wrong!!,'''Mike Walsh'',Intermediate"; 
$subst = "''"; 
$result = preg_replace($re, $subst, $str);

Output:

You''re Doing It Wrong!!,''''Mike Walsh'',Intermediate
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563